Union of 2 matches and ordering by relevance of both

Databases
1

Hi,

I’m trying to query two unrelated tables (but retrieve related date). I’ve done this previously as 2 separate queries, but I’m trying to reduce the number of queries to increase performance.

The 2 queries I use, are match queries (in Boolean Mode). I’m not an expert, but I’m trying to get this to perform as well as it can, and have read Match & Boolean Mode for searching over text columns is probably what I should be using. The problem I’m having is that I can order by one score but not the other:


SELECT URL, SUM, Title, 'dummy1', 'dummy2', 'dummy3', 'dummy4', 
  MATCH(TAG,SUM,Title) 
  AGAINST ('Landlord') as RelevanceA
  FROM rft_searchlawssum 
  WHERE MATCH (TAG,SUM,Title) AGAINST('Landlord' IN BOOLEAN MODE)
UNION ALL
SELECT g.URL as gURL, p.message AS pmessage, p.subject as psubject, g.idtype as gidtype, g.id as gid, p.pid as ppid, p.tid as ptid,   
  MATCH(p.message,p.subject) AGAINST ('Landlord') as RelevanceB
  FROM mybb_google_seo g LEFT JOIN mybb_posts p ON g.id = p.tid 
  WHERE MATCH(p.message,p.subject) 
  AGAINST('Landlord' IN BOOLEAN MODE) AND g.idtype ='4' 
  ORDER BY RelevanceA, RelavanceB DESC

If I drop the RelavanceB this works, but with the RelavanceB I get:
#1054 - Unknown column ‘RelevanceB’ in ‘order clause’

Any idea why I cant use RelavanceB in my order clause?

2

The ordering happens before the union. If you want to order the whole thing after the union, you have to use a subquery.

3

sorry, the ordering happens after the UNION

there is no RelevanceB column in the given query because all the column names for the result set (which is what the ORDER BY operates on) are taken from the first SELECT

i suspect that shoehorning these two unrelated searches into a common UNION query format is more trouble than it’s worth – just use two search queries

4

well, I also want to use union so I can use limit 0,19 for the first page and 20,39 for the second, etc.

If its there is no union, it makes getting the 1st page of results and 2nd page of results quite tricky (possibly more so that figuring out how to union these select statements). I’ve rearranged it since (but I still have the same issue)

SELECT g.URL as gURL, p.message AS pmessage, p.subject as psubject, g.idtype as gidtype, g.id as gid, p.pid as ppid, p.tid as ptid,   
  MATCH(p.message,p.subject) AGAINST ('Landlord') as RelevanceB
  FROM mybb_google_seo g LEFT JOIN mybb_posts p ON g.id = p.tid 
  WHERE MATCH(p.message,p.subject) 
  AGAINST('Landlord' IN BOOLEAN MODE) AND g.idtype ='4' 
UNION ALL
SELECT URL, SUM, Title, 'dummy1', 'dummy2', 'dummy3', 'dummy4', 
  MATCH(TAG,SUM,Title) 
  AGAINST ('Landlord') as RelevanceA
  FROM rft_searchlawssum 
  WHERE MATCH (TAG,SUM,Title) AGAINST('Landlord' IN BOOLEAN MODE)
ORDER BY RelevanceB DESC

I now cant order by RelevanceA, does anyone know a good reference for this sort of query (Union of matched statements)

5

using a union for paging is fine

so you’ve got two types of result rows, right? i mean, they certainly look a lot different (sum in a message column, title in a subject column, etc.), but the thing you have to remember is that what you think there is in two different columns (RelevanceA and RelevanceB) are actually in the same column, and that column’s name is whatever it’s called in the first SELECT in the union

do you want these different row types interleaved according to overall relevance, or do you want the rows printed out separately, and if so, which ones go first?

also, how do you distinguish which type of row it is? obvioulsy they are going to be displayed differently, right?

6

Hmm… I was certain it happens before… ah, well, no one’s perfect… :smiley: