MySQL Forums
Forum List  »  Optimizer & Parser

Re: Order By Slows down Joins
Posted by: Rick James
Date: August 23, 2012 08:56PM

The "derived" table (the subquery) has 1176 rows. Subqueries have no indexes. The subquery is handled last, as if there were no practical way to start with it. Hence, it hand to scan all 1176 rows repeatedly.

for reference:
    FROM  files f
    LEFT JOIN  xfiles x  ON x.hash = f.bhash
    LEFT JOIN  cate c  ON = f.cate
    LEFT JOIN  users u  ON = f.user
    LEFT JOIN  userslev ul  ON u.id_lev =
      ( SELECT  DISTINCT x.hash, s.iis
            FROM  anno x
            JOIN  seb s ON s.iis = x.icc
            WHERE  x.le =0
              AND  ( x.eve =0  OR  x.eve =2 )
            GROUP BY  `x`.`hash`
       ) AS tabel2  ON tabel2.hash = f.bhash
    WHERE  f.see + IFNULL(x.see, 0) + f.lee + IFNULL(x.lee, 0) > 0
    ORDER BY  f.added DESC

> Edit Drop bhash BTREE No No bhash (20) 27815 A
Does that mean that you have a "prefix" index? That may be a mistake.
What is the datatype of bhash?
What is the datatype of anno.hash?

Please provide SHOW CREATE TABLE (for each table).

Do you need LEFT JOIN? Won't JOIN do provide the same answer, at least for tabel2?

Do you need all 27819 rows?

One approach is to create a temp table with the subquery, and index it on hash.

Options: ReplyQuote

Written By
August 22, 2012 09:12AM
Re: Order By Slows down Joins
August 23, 2012 08:56PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.