MySQL order by priority

Question

Hi I'm building a Java program, where i need to manage some items. In order to do that I need to filter the items. I filter items by building SQL query and executing it. I need the following query to first sort (LEFT JOIN (SELECT * FROM grupe ORDER BY grupe.pavadinimas DESC)grupe ON) AND only then add all the other data and execute WHERE clause.

SELECT  * 
FROM    (
      (
        SELECT  preke.* 
        FROM    preke_tiekejas
                , preke 
        WHERE   preke_tiekejas.tiek_id IN (18610,13604) 
                AND preke.pr_id=preke_tiekejas.pr_id 
        GROUP BY 
                preke_tiekejas.pr_id
      ) preke
      , preke_kaina
      , (
        SELECT  * 
        FROM    preke_info 
        WHERE   preke_info.pavadinimas LIKE '%kait%'
      ) preke_info
    ) LEFT JOIN (
      SELECT  * 
      FROM    grupe 
      ORDER BY 
              grupe.pavadinimas DESC
    )grupe ON grupe.pgs_id=preke.pgs_id 
    LEFT JOIN gamintojas ON gamintojas.gam_id=preke.gam_id 
    LEFT JOIN grupe_darb ON grupe_darb.pgs_id=grupe.pgs_id 
WHERE   preke_kaina.pr_id=preke.pr_id 
    AND preke_info.pr_id=preke.pr_id 
    AND grupe_darb.darb_id = 20 
LIMIT 0, 500

If I remove these two subqueries :

(SELECT * FROM preke_info WHERE preke_info.pavadinimas LIKE '%kait%')

(SELECT preke.* FROM preke_tiekejas, preke
WHERE preke_tiekejas.tiek_id IN (18610,13604)
AND preke.pr_id=preke_tiekejas.pr_id
GROUP BY preke_tiekejas.pr_id)

then the query works the way I want.

P.S. order it only in the end of the query is not an option, because there is huge amount of records and then it becomes slow as hell.

Explain

@Lieven:

queries results are NOT same - without limit should be ~90k rows:

queries results are NOT same - return ~50rows:

Answer 1

I find it hard to read your query, I would reformat it to this

Reformatted

SELECT  * 
FROM    (
          (
            SELECT  preke.* 
            FROM    preke_tiekejas
                    , preke 
            WHERE   preke_tiekejas.tiek_id IN (18610,13604) 
                    AND preke.pr_id=preke_tiekejas.pr_id 
            GROUP BY 
                    preke_tiekejas.pr_id
          ) preke
          , preke_kaina
          , (
            SELECT  * 
            FROM    preke_info 
            WHERE   preke_info.pavadinimas LIKE '%kait%'
          ) preke_info
        ) LEFT JOIN (
          SELECT  * 
          FROM    grupe 
          ORDER BY 
                  grupe.pavadinimas DESC
        )grupe ON grupe.pgs_id=preke.pgs_id 
        LEFT JOIN gamintojas ON gamintojas.gam_id=preke.gam_id 
        LEFT JOIN grupe_darb ON grupe_darb.pgs_id=grupe.pgs_id 
WHERE   preke_kaina.pr_id=preke.pr_id 
        AND preke_info.pr_id=preke.pr_id 
        AND grupe_darb.darb_id = 20 
LIMIT 0, 500

After reformatting, I would remove the implicit JOIN syntax. You should always try to use explicit JOIN's for readability and maintainability. The implicit JOIN syntax will sooner or later become obsolete.

Using explicit JOINS

SELECT  * 
FROM    ( (
            SELECT  preke.* 
            FROM    preke_tiekejas
                    INNER JOIN preke ON preke.pr_id=preke_tiekejas.pr_id 
            WHERE   preke_tiekejas.tiek_id IN (18610,13604)                     
            GROUP BY 
                    preke_tiekejas.pr_id
          ) preke
          INNER JOIN preke_kaina preke_kaina.pr_id=preke.pr_id 
          INNER JOIN (
            SELECT  * 
            FROM    preke_info 
            WHERE   preke_info.pavadinimas LIKE '%kait%'
          ) preke_info ON preke_info.pr_id=preke.pr_id 
        ) LEFT JOIN (
          SELECT  * 
          FROM    grupe 
          ORDER BY 
                  grupe.pavadinimas DESC
        )grupe ON grupe.pgs_id=preke.pgs_id 
        LEFT JOIN gamintojas ON gamintojas.gam_id=preke.gam_id 
        LEFT JOIN grupe_darb ON grupe_darb.pgs_id=grupe.pgs_id 
WHERE   grupe_darb.darb_id = 20 
LIMIT   0, 500

Now the redundant subqueries stand out. I might be off but I believe your statement can be further reduced to this

Removing redundant subqueries

SELECT  * 
FROM    (
          SELECT  preke.* 
          FROM    preke_tiekejas
                  INNER JOIN preke ON preke.pr_id=preke_tiekejas.pr_id 
          WHERE   preke_tiekejas.tiek_id IN (18610,13604)                     
          GROUP BY 
                  preke_tiekejas.pr_id
        ) preke
        INNER JOIN preke_kaina preke_kaina.pr_id=preke.pr_id 
        INNER JOIN preke_info ON preke_info.pr_id=preke.pr_id 
        LEFT OUTER JOIN grupe ON grupe.pgs_id=preke.pgs_id 
        LEFT OUTER JOIN gamintojas ON gamintojas.gam_id=preke.gam_id 
        LEFT OUTER JOIN grupe_darb ON grupe_darb.pgs_id=grupe.pgs_id 
WHERE   grupe_darb.darb_id = 20 
        AND preke_info.pavadinimas LIKE '%kait%'
ORDER BY 
        grupe.pavadinimas DESC
LIMIT   0, 500

Now, to me, this is a query I can work with. Unfortunatly, I don't see any glaring performance problems with it that proper indexing can't solve.

Can you show us the execution plan for your query?