ORDER BY making MySQL query run very slow

Question

I have the following MySQL query:

SELECT 
   table1.*, table2.*    
FROM 
   `Table1` AS table1, 
   `Table2` AS table2, 
   `Table3` 
WHERE 
   table1.col1 = table2.col1 AND
   table1.col1 = Table3.col1 AND  
   table1.col1 != '0' AND 
   table1.col1 NOT IN (1,2) 
   table1.col2 LIKE 'A' AND 
   (table1.col3 LIKE 'A' OR table1.col3 LIKE 'B') AND 
   Table3.col4 = 0 AND 
   Table3.col5 = 0 AND 
   Table3.col6 = 0 
ORDER BY 
   table1.col10 ASC 
LIMIT 0, 5

Without the ORDER BY statement, it executes in 0.002 seconds.

With the ORDER BY statement, it executes in 2 seconds.

I saw this answer which seemed to work for the OP, but when I tried it on mine I got a Duplicate column name 'col1' error.

Any ideas on how I can speed this up with the ORDER BY ?

EDIT:

As requested, below is the modified Query that I tried based on the link above which gave me the Duplicate column name 'col1' error:

SELECT * FROM (
    SELECT 
       table1.*, table2.*    
    FROM 
       `Table1` AS table1, 
       `Table2` AS table2, 
       `Table3` 
    WHERE 
       table1.col1 = table2.col1 AND
       table1.col1 = Table3.col1 AND  
       table1.col1 != '0' AND 
       table1.col1 NOT IN (1,2) 
       table1.col2 LIKE 'A' AND 
       (table1.col3 LIKE 'A' OR table1.col3 LIKE 'B') AND 
       Table3.col4 = 0 AND 
       Table3.col5 = 0 AND 
       Table3.col6 = 0 
) AS t1
ORDER BY 
   table1.col10 ASC 
LIMIT 0, 5

Apparently, this is supposed to ensure the ORDER BY is only done after the final result set is determined.

This query as it stands won't work. You might want to edit the question. General advice for speeding up queries: ensure you have the correct indexes and don't `select *` if you don't need all that data. The less you return the less work has to be done. If you got a duplicate column name error that's because you've probably got a duplicate column name give one of your columns with the same name an alias ( another reason not to use `*` ). — Ben, Dec 04 '11 at 17:51
Your query won't run. "Table3.col6 = 0 AND" the "AND" part is missing. — Udo Held, Dec 04 '11 at 17:52
Try to take a look at: http://www.mysqlperformanceblog.com/2010/10/25/impact-of-the-sort-buffer-size-in-mysql/ — Nonym, Dec 04 '11 at 18:11
@Programmer That will happen because `col1` exists in both tables and you're not differentiating between the two. Try explicitly selecting all the columns you need for `table1` and `table2`. — Michael Mior, Dec 04 '11 at 18:24

score 0 · Answer 1 · answered Dec 04 '11 at 17:50

0

Try adding an index on table1 and col10 .

answered Dec 04 '11 at 17:50

Udo Held

12,314
11
67
93

They already have an index. This query runs very fast without the ORDER BY, but when I add it, it makes it run too slow. Any other ideas? – ProgrammerGirl Dec 04 '11 at 18:03

score 0 · Answer 2 · answered Mar 30 '12 at 13:42

You can also explore to use temp table

SELECT
   table1.*, table2.* INTO #temp
FROM 
   `Table1` AS table1, 
   `Table2` AS table2, 
   `Table3` 
WHERE 
   table1.col1 = table2.col1 AND
   table1.col1 = Table3.col1 AND  
   table1.col1 != '0' AND 
   table1.col1 NOT IN (1,2) 
   table1.col2 LIKE 'A' AND 
   (table1.col3 LIKE 'A' OR table1.col3 LIKE 'B') AND 
   Table3.col4 = 0 AND 
   Table3.col5 = 0 AND 
   Table3.col6 = 0 

SELECT * FROM #temp  
ORDER BY col10 ASC 
LIMIT 0, 5

ORDER BY making MySQL query run very slow

2 Answers2