StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL Query optimization with number of tables join and order by with limit clause
text
Body
copied!<p>I have a query which has multiple tables joined using distincct - left join - order by - limit clause.</p> <p>The query looks like this:- </p> <pre><code>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; </code></pre> <p>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.</p> <p>What I am wondering in this query is :- this query runs within seconds if I run it without order by clause. something like this :- </p> <pre><code>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; </code></pre> <p>and if I run it with order by clause then it takes 30-40 seconds to execute.</p> <p>I tried using the index hint functionality provided by <code>mysql:- USE INDEX FOR ORDER BY (idx_h_col5)</code> but I am getting syntax error while executing this query. The error message says incorrect syntax near </p> <p>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.</p> <p>Any input will be great help.</p> <p>Thanks in advance.</p> <p>Regards, Manasi</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload