3
I have the following query:
SELECT type,name,points FROM clients WHERE type='1' ORDER BY name
I created an index for the "clients" table only above the "type field".
Adding an ORDER BY on top of a non-indexed field will affect the query response time?
2
Yes. Notice what the reference manual:
If you want to have a higher ORDER BY speed, first you must see if you can make Mysql use indexes...
But the practical question is how relevant is this time? This answer can only be given with the optimization analysis of your queries.
In this manual you will find all the reference you need to perform this analysis: Optimizing Selects and Other Queries.
Already removed, thank you
Browser other questions tagged mysql optimizing índices
You are not signed in. Login or sign up in order to post.
Your ID field is not a primary key?
– bfavaretto
That’s what @Bacco said. To see which indexes are actually used, run
EXPLAIN SELECT points FROM clients WHERE type='1' ORDER BY id.– bfavaretto
@Bacco the id index exists in the table, what I wanted to understand is if in the above query it makes a difference to have the ID as an index by using ORDER BY ID in the query.
– Filipe Moraes
@bfavaretto the index id exists in the table, what I wanted to understand is if in the query above it makes difference to have the ID as an index by using ORDER BY ID in the query.
– Filipe Moraes
@Bacco did not explain me well, I changed the question and I think it is now much clearer. Using id was not a good example, I changed the example too.
– Filipe Moraes
First a field index as type will probably not be used as it will be a field with few different values (low cardinality), the optimizer will opt for full table scan, ORDER BY however has a computational time and depending on the index and columns can be used.
– Motta
Note, read about Oracle bitmap indexes for this issue of cardinality. Question of use of indexes or not depends on many factors beyond the existence of the same and its use in the WHERE.
– Motta