Can I prevent using filesort in mysql when field on which condition in one table and field on which order in another. Can use index in this situation? Both tables are large - more than 1 million records
+3
A:
You are dealing with 1 million records, definitely you need to add indexing to gain some speed otherwise it will be overkill anyone visiting you site.
You need to closely examine which fields you will be add indexing to. Thanks
Sarfraz
2010-02-16 08:45:48
Thanks, but mysql using indexes for "order by" and "where" operations only if on all fields using in "where" clause and order by exists index. But can I create such index when this fields in different tables?
Andrew
2010-02-16 09:01:32
Of course I can duplicate field on which order by in both tables but this decrease insert/update/delete operation performance and increase usage disc space :)
Andrew
2010-02-16 09:03:57
@Andrew: yes you have to decide whether you want better performance at selecting or updating the database.
Sarfraz
2010-02-16 11:22:47
A:
You need to carefully look at the indexes using explain.
If there are where clauses, either add the column you are ordering by to the index being used for that table as well and see if it gets rid of the filesort, or if its not currently using one for that table (which I doubt is the case with that many records) then just create a new one.
Also worth flagging, which someone else may be able to offer more info on, is that mysql often (or always?) can't use an index to sort DESC - I've been in a situation before where its been performant to have a computed field that will index in reverse order, and order by that ASC.
EG: If you have an integer field that you want to order by DESC, add a field where you store the integer value subtracted from 1000000000 (or some other large number), index it, and order by that field ascending.
As I say, I can't remember specifics, it may affect older mysql only or something, but have a feeling its a current limitation.
benlumley
2010-02-16 08:50:29