MySQL order of priority
Hi, I am building a Java program where I need to manage some elements. For this I need to filter the elements. I am filtering the items by creating a SQL query and executing it. I need the following query for the first sort ( LEFT JOIN (SELECT * FROM grupe ORDER BY grupe.pavadinimas DESC)grupe ON
) And only then add all the other data and execute the 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 request works the way I want.
PS It is impossible to order it only at the end of the request, since there are a huge number of records, and then it becomes damn slow.
Explain
@Lieven :
query results are NOT the same - no limit should be ~ 90k rows:
the query results are NOT the same - we return ~ 50rows:
source to share
I find it difficult to read your request, I would reformat it to
Modified
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 formatting, I'll remove the implicit syntax JOIN
. You should always use explicit JOIN
for readability and maintenance. The implicit syntax JOIN
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
Extra subqueries are now allocated. I could be off, but I believe your statement can be boiled down 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 for me this is a request that I can work with. Unfortunately, I don't see any major performance issues that proper indexing can't solve.
Can you show us a plan for fulfilling your request?
source to share