MySQL Forums
Forum List  »  Performance

Re: Filesort & Order by
Posted by: Scott Nemes
Date: May 30, 2012 11:02AM

Keep in mind that the EXPLAIN results for a test data set that is not reflective of your actual data set may vary quite a bit. You'll also want to do some performance tests on the queries before and after indexing, as even though the EXPLAIN plan may look better it does not guarantee it will actually perform better. =)

That aside, you could add a covering index:

alter table test add index idx_name_code (Name, Code);

mysql> explain SELECT * FROM test WHERE Name <> "blbla" ORDER BY Name;
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+--------------------------+
| 1 | SIMPLE | test | index | Index 2,idx_name_code | idx_name_code | 67 | NULL | 10 | Using where; Using index |
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

--
Scott Nemes
MySQL DBA

http://www.linkedin.com/in/scottnemes
http://www.twitter.com/ScottNemes

Options: ReplyQuote


Subject
Views
Written By
Posted
1656
May 30, 2012 02:19AM
Re: Filesort & Order by
875
May 30, 2012 11:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.