We have 2 SELECT that are being joined via UNION.
The code being like this:
(SELECT x, y, z
FROM swd, swv
WHERE swv .keyword = ‘$find’
AND likes > 0
AND swv .meta_id = swd.id
AND display = ‘yes’
ORDER BY likes DESC)
UNION
(SELECT x, y, z
FROM swd
WHERE MATCH (kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST(‘$find’)
AND display = ‘yes’
LIMIT $start, $page)
So, when the 1st SELECT is run by itself it does correctly return the results ORDERed BY number of likes DECS
However, when the 2 SELECTS are run together in the UNION, the ORDER BY DESC of the 1st SELECT is lost!
What is going on?
What do we need to do to maintain ORDER BY DESC when the 2 SELECTS are in UNION?
UNIONS cause orders to be lost as it does a match up. You can fake it by adding a bogus limit to the union, then order the whole result.
SELECT x
, y
, z
FROM (SELECT x
, y
, z
, limit
FROM swd
JOIN swv ON swv.meta_id = swd.id AND swv.keyword = '$find'
WHERE likes > 0
AND display = 'yes'
UNION
SELECT x
, y
, z
, -1 AS limit
FROM swd
WHERE MATCH (kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find')
AND display = 'yes'
LIMIT $start, $page) sq
ORDER BY limit DESC
simple – an ORDER BY is valid only on the results of the union, not on the individual subqueries
did you know that when you specify UNION without the ALL keyword, the database has to take the entire combined result set and sort it into sequence by all the columns in the SELECT clauses?
it does this so that it can detect and remove duplicate result rows
so then if you have an ORDER BY clause, the entire result set, minus duplicates, is then sorted all over again
I cannot make sense of your MySQL code as applied to my fake code to take it to the real code.
So here is the real code, can you please give your suggestion to correct this lost ORDER BY problem to the real code, here:
(SELECT swd.id, ix_id, title, descpt, category
FROM swd, swv
WHERE swv.keyword = '$find'
AND swv.vote_up > 0
AND swv.meta_id = swd.id
AND swd.display = 'yes'
ORDER BY swv.vote_up DESC)
UNION
(SELECT id, ix_id, title, descpt, category
FROM swd
WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find')
AND display = 'yes'
LIMIT $start, $page)
All I did was reformat your query to use the join syntax - which the dbms will do anyways, so my way is slightly more efficient. But essentially what I did was make your query a sub-query (except for the order in the first query because it’s redundant due to the UNION) I take the result set from the UNION and sort it.
SELECT swd.id
, ix_id
, title
, descpt
, category
FROM (SELECT swd.id
, ix_id
, title
, descpt
, category
, swv.vote_up
FROM swd
INNER JOIN swv ON swv.meta_id = swd.id
AND swv.vote > 0
WHERE swv.keyword = '$find'
AND swd.display = 'yes'
UNION
SELECT id
, ix_id
, title
, descpt
, category
, -1 AS vote_up
FROM swd
WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find')
AND display = 'yes'
LIMIT $start
, $page) SQ
ORDER BY swv.vote_up DESC
Or if you want to see it without optimizing yours
SELECT swd.id, ix_id, title, descpt, category
FROM ((SELECT swd.id, ix_id, title, descpt, category
FROM swd, swv
WHERE swv.keyword = '$find'
AND swv.vote_up > 0
AND swv.meta_id = swd.id
AND swd.display = 'yes'
ORDER BY swv.vote_up DESC)
UNION
(SELECT id, ix_id, title, descpt, category
FROM swd
WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find')
AND display = 'yes'
LIMIT $start, $page) SQ
ORDER BY swv.vote_up DESC
These suggestions do not work, as I suspected since the
swv.vote_up
only exists in the 1st Table. That is why we were doing the UNION to start with since one Table is based on vote_up
but not the other Table.
FYI, MySQL gives Error message:
Table ‘swv’ from one of the SELECTs cannot be used in global ORDER clause
okay, i will help you fix this problem, if you would kindly test and then comment on each of the following two queries
first query –
SELECT swd.id
, swd.ix_id
, swd.title
, swd.descpt
, swdcategory
FROM swd
INNER
JOIN swv
ON swv.meta_id = swd.id
AND swv.keyword = '$find'
AND swv.vote_up > 0
WHERE swd.display = 'yes'
second query –
SELECT id
, ix_id
, title
, descpt
, category
FROM swd
WHERE MATCH(title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10)
AGAINST('$find')
AND display = 'yes'
do these two queries separately produce the correct results? please also explain why the second query appears to have paging ($start,$page) but the first one doesn’t
No. The 1st SELECT is way off the Mark. It will not work at all since the vote_up is coming from the 2nd Table of swv.
I see that my question is too difficult to answer via this forum. And by the time I explain the complexities of the problem that will become a problem on to its own.
So I have to work out the problem myself.
Thank anyway.