Mysql query optimization

[s0h31l]

Hello everyone...

Im trying to optimize my queries...

I have a question with the ORDER BY command...

What should I consider when i'm using ORDER BY, (for example does indexing helps ?)

Here is an example query which shows the last four females that entered the website:

SELECT *
FROM users
WHERE gender = 0
ORDER BY last_enter DESC
LIMIT 0 , 4

Here is the explain result:

id ... select_type . table . type . possible_keys . key . key_len . ref . rows ............ Extra
1 ........ SIMPLE ...users . ref ...... gender ... gender .... 4 ... const . 30962 .. Using where; Using filesort

To me this gonna take long time, because mysql have to sort 30000 rows....

Thanks

Soheil

[LifeinAsia]

Definitely have an index on the gender field. I also recommend that you make it an integer value- numeric searches are much faster than character searches. You should also consider selecting only the fields that you will actually use instead of *.

Sorting 30,000 rows is not a problem. We regularly deal with tables having more than 10 million rows and queries against properly indexed tables are lighting fast.