Hi.

Y have a table with all the needed indexes defined in particular field let's
say Afield

I have defined a view from table WHERE Afield >= 5


When I run a single EXPLAIN SELECT * from TableView, it recomends to use
as possible key Afield but it uses filesort

Ok,

¿How can I define the view in order to use a previously created table index
by Afield ? something like USE INDEX or FORCE INDEX

Thanks in advance.

--
..

"CENTRINO" <none (AT) nonelandia (DOT) com> wrote:

This is inconsistent. This WHERE does not require sorting.
And in general sorting is not an alternative to using an index.

You cannot. If you want control over index usage, don't use VIEWs.


XL

Thanks Axel, but what a dissapointment !!!

Views are one of the most important features in a relational system !! They
reduce developement effort and inprove final user hability to extract data.

My particular wish list for MYSQL:

- Virtual or calculated colums in views
- Indexes for views ....





"Axel Schwenke" <axel.schwenke (AT) gmx (DOT) de> escribió en el mensaje
news:56hin8-bp8.ln1 (AT) xl (DOT) homelinux.org...

"CENTRINO" <none (AT) nonelandia (DOT) com> wrote:

I disagree. Views are syntactic sugar.
Nice to have, but far from being essential.
OTOH views have the potential to increase query
execution complexity beyond any limit (by nesting).

Whut? Any column in a VIEW is virtual, so to speak.
And of course you can use arbitrary expressions
when you define a view.

If you need indexes on the view as such, then you
have to materialize it. Indexes on the underlying
tables will be used - if the optimizer thinks it
would make sense. You just cannot use index hints
when selecting from a view. Or when you define it.
The latter does not make much sense anyway.


XL

On 10/25/2011 01:56 PM, CENTRINO wrote:
You can certainly have calculations in a view ... all you have to do is
to alias the column name. Can you give us an example of some calculation
that won't work in a view?

As to indexes, if the WHERE clause is part of the view's SQL statement,
the index will be used. There is a trick or workaround you can use to
get the view to use an index, though: You create a function which
returns the value of a user variable which you set to the WHERE
criterium's value right before selecting from the view. Unfortunately,
user variables are not allowed in a view's definition, but functions
are. This is explained on the MySQL web site in the first user comment
at the bottom of the page...

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

This can come in very handy, especially when filtering results of a
UNION SELECT which would perform horribly otherwise.

On 10/25/2011 02:59 PM, Robert Hairgrove wrote:
Actually, you don't even have to do that, but you won't be happy with
the auto-generated name, probably.

Axel Schwenke wrote:

I always enjoy reading your posts: to the nub of the matter in two
sentences..