Query optimization with number of tables join and order by with limit clause

I have a query which has multiple tables joined using distincct - left join - order by - limit clause.

The query looks like this:-

Select DISTINCT a.col1, b.col2, c.col3, d.col4, e.col5, f.col6, g.col7, h.col8, 
i.col9, j.col10
From test_a a 
left join test_b b on a.col1 = b.col2 
left join test_c c on c.col1 = d.col2 
left join test_d d on d.col1 = c.col2 
left join test_e e on e.col1 = d.col2 
left join test_f f on f.col1 = e.col2 
left join test_g g on g.col1 = f.col2 
left join test_h h on h.col1 = a.col1 
left join test_i i on i.col1 = f.col2 
left join test_j j on j.col1 = i.col2
Where a.col2 = 'Y' 
and c.col4  = 1
Order by h.col5 desc 
limit 50;

All the column used the in coditions has index on it. And explan output of this query gives resultset where I can see it uses all the index properly and total rows it scanned from all the tables is 18000.

What I am wondering in this query is. It runs within seconds if I run it without order by clause. Something like:

Select DISTINCT a.col1, b.col2, c.col3, d.col4, e.col5, f.col6, g.col7, h.col8, 
i.col9, j.col10
From test_a a 
left join test_b b on a.col1 = b.col2 
left join test_c c on c.col1 = d.col2 
left join 开发者_如何学运维test_d d on d.col1 = c.col2 
left join test_e e on e.col1 = d.col2 
left join test_f f on f.col1 = e.col2 
left join test_g g on g.col1 = f.col2 
left join test_h h on h.col1 = a.col1 
left join test_i i on i.col1 = f.col2 
left join test_j j on j.col1 = i.col2
Where a.col2 = 'Y' 
and c.col4  = 1
limit 50;

And if I run it with order by clause then it takes 30-40 seconds to execute.

I tried using the index hint functionality provided by mysql:- USE INDEX FOR ORDER BY (idx_h_col5), but I am getting syntax error while executing this query. The error message says incorrect syntax near

I have one composite index on the column used in order by clause. I also tried creating a single index on this column but nothing really works.

---

MySQL can use keys for sorting instead of sorting the result after fetching the data, but only if several conditions are met. You can see a list of these conditions here: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

In your case, I think that the multiple JOINs prevent the quick sorting. One of the mentioned cases in which MySQL can't use an index for sorting is:

You are joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)

I am not sure if there is a way around it. It depends on the tables structure and the actual query.

To get more help, try posting the explain output of the ordered query.

---

I would first try adding compound indices on:

Table a
    (col2, col1)

Table b
    (col4, col1)

and a simple index on

Table h
    (col5)