SQL Statement loads very slow with Order By

13
January 13, 2019, at 07:30 AM

I have this sql statement below and returns in 0.149seconds:

SELECT Trans.Transid, TransDetail.TransDetailID FROM Trans  
LEFT JOIN TransDetail ON Trans.Reference = TransDetail.TransID 
LIMIT 1000

I modified by adding Order By and it grealy slows down the return to 1min 20 seconds

SELECT Trans.Transid, TransDetail.TransDetailID FROM Trans  
LEFT JOIN TransDetail ON Trans.Reference = TransDetail.TransID 
ORDER BY Trans.Transid, TransDetail.TransDetailID 
LIMIT 1000

These are so far what I done but still didn't improve the speed:

1.) Added Index for Trans.TransID, Trans.Reference, and TransDetail.TransDetailID

2.) Tried putting the Limit to even 1 but still returns very slow

3.) Tried doing ORDER BY Trans.Transid and it returns in 0.223 seconds

4.) Tried doing ORDER BY TransDetail.TransDetailID and it returns in 48.365 seconds

Doing the TransDetail.TrasnsDetailID only in Order by is slower compared than having the Trans.TransID. But doing both makes it very very slow.

What's the best way to do order by to make it faster?

Answer 1

I suggest to use 2 limits.
First on the smallest & fastest table.
Then on the result of the left join.

SELECT tr.Transid, det.TransDetailID 
FROM 
(
    SELECT Transid, Reference
    FROM Trans
    ORDER BY Transid
    LIMIT 1000
 ) tr
LEFT JOIN TransDetail det ON det.TransID = tr.Reference
ORDER BY tr.Transid, det.TransDetailID 
LIMIT 1000

It would benefit from an index on Trans.TransID for the sub-query. But I assume it's the primary key on that table anyway.
And an index on TransDetail.TransID for the join on TransDetail.

Small test here

READ ALSO
Python mysql connector query same result every time

Python mysql connector query same result every time

I want to access a mysql database via mysqlconnector in python 2

30
Is there a way to fetch string value from MySQL database through Karate

Is there a way to fetch string value from MySQL database through Karate

I am having an issue while karate fetches a string value from MySQL dbreadValue works fine if it is string value it fetches from db but getting error like Unknown column 'SMEMSD22' in 'where clause' How to deal with alphanumeric data with karate

24
Column count doesn't match value count at row 1 in MySQL

Column count doesn't match value count at row 1 in MySQL

I have created a table with the following query:

16