Adding ORDER BY clause to MySQL query makes it return in ~30 seconds, up from ~0.5

Question

So I have this query that is relatively fast at ~0.5 seconds but when I add an ORDER BY clause it jumps up to nearly 30 seconds.

Original query: (returns in ~0.5 seconds)

SELECT table1.*,table2.* FROM table1 LEFT OUTER JOIN table2 ON table1.column2=table2.column3 WHERE table1.column1='value' LIMIT 4

Query with ORDER BY: (returns in ~30 seconds)

SELECT table1.*,table2.* FROM table1 LEFT OUTER JOIN table2 ON table1.column2=table2.column3 WHERE table1.column1='value' ORDER BY table1.column4 DESC LIMIT 4

Note I added an index to the column that is being used by the ORDER BY and it changed nothing.

Any ideas as to what would be causing this?

Answer 1

This takes longer because the query can't just pick the first 4 items it finds. It has to order the entire list and then choose the top 4 from that.

Fix this by adding an index which includes table1{column4, ...}. If you only need a few columns from table 1 (and they're narrow), I'd add them all to the index (covering index).

If indexed properly, the SQL engine can pull just the first four columns that you want--not the entire set.

If you do have indexing and it's not helping, run the query with EXPLAIN to see what the execution plan looks like (good tip, @IronGoofy):

EXPLAIN 
  SELECT table1.*,table2.* 
  FROM table1 
  LEFT OUTER JOIN table2 ON table1.column2=table2.column3 
  WHERE table1.column1='value' ORDER BY table1.column4 DESC LIMIT 4

Answer 2

How are you running the query?

It is common for some tools to retrieve only the first 100 or so records, and pull down more as needed.

Adding the ORDER BY forces the tool to retrieve the ENTIRE dataset.

If you are in MySql browser, try running w/o the ORDER BY, and then use CTRL-END to scroll to the bottom of the datagrid. How long does that take?

Answer 3

Is table1.column1 indexed? If yes, then the query optimizer will use that index to select the initial set of rows from table1, since it's at worst an index range scan (very fast).

If this query is one that's run frequently, you may get the performance you want by indexing on (column1,column4). I don't know MySQL very well, but with Oracle you could boost performance even more by indexing (column1,column4,column2), which would make the optimizer do all of its work from the index, and not touch table data at all.

However, adding indexes is a tradeoff: it will increase the time taken by every insert (or update of the indexed columns), makes your database larger, and may cause an overall slowdown as scarce memory resources (ie, buffer cache) get assigned to the new index.

Answer 4

Concur with Michael's explanation, +1.

As to the index not making a difference, have a look at the execution plan (not sure how to do that in MySQL - maybe someone can edit this in?). Again, I agree with Michael that this should make things faster (as long as column4 is "selective").

@kogus: retrieving the whole resultset to the client is not the same as ordering the resultset, the ordering should take place on the server without the need to transfer all results over the network

Answer 5

Try running explain:

EXPLAIN SELECT table1.*,table2.* FROM table1 LEFT OUTER JOIN table2 ON table1.column2=table2.column3 WHERE table1.column1='value' ORDER BY table1.column4 DESC LIMIT 4

This will probably tell you MySQL is doing a filesort. Can you put an index on (column1, column4)?

Can you tell something more about your model? What indexes are you using? Can you show some explain output? What kind of types are using for the fields?

Answer 6

Agreed on the index stuff mentioned by Michael.

Additionally, in MySQL, you can learn a lot about the performance your query will have by examining the results of prepending EXPLAIN to your query, e.g.

EXPLAIN SELECT * FROM foo_tbl WHERE foobar = 'foo'

will help you design your queries better, and index appropriately. Read up on EXPLAIN syntax and Optimizing queries with EXPLAIN.