MySQL - Faster method for this complex query?

Question

Is there a less resource intensive / faster way of performing this query (which is partly based upon: This StackOverflow question ). Currently it takes 0.008 seconds searching through only a dozen or so rows per table.

SELECT DISTINCT *
FROM (
(

SELECT DISTINCT ta.auto_id, li.address, li.title, GROUP_CONCAT( ta.tag ) , li.description, li.keyword, li.rating, li.timestamp
FROM tags AS ta
INNER JOIN links AS li ON ta.auto_id = li.auto_id
WHERE ta.user_id =1
AND (
ta.tag LIKE '%query%'
)
OR (
li.keyword LIKE '%query%'
)
GROUP BY li.auto_id
)
UNION DISTINCT (

SELECT DISTINCT auto_id, address, title, '', description, keyword, rating, `timestamp`
FROM links
WHERE user_id =1
AND (
keyword LIKE '%query%'
)
)
) AS total
GROUP BY total.auto_id

Thank you very much,

Ice

Answer 1

I would hope that the query optimizer would do this for you, but you might want to try doing the select on tags by user_id before doing the join just in case in the first subquery. This would reduce the number of rows that you would have to join across presumably. You also probably want to have indexes on auto_id AND user_ID.

SELECT DISTINCT *
FROM (
   (SELECT ta.auto_id, li.address, li.title, GROUP_CONCAT( ta.tag ),
           li.description, li.keyword, li.rating, li.timestamp
    FROM (SELECT auto_id, tag FROM tags WHERE user_id = 1) AS ta
         INNER JOIN links AS li ON ta.auto_id = li.auto_id
         WHERE (ta.tag LIKE '%query%') OR (li.keyword LIKE '%query%')
    GROUP BY li.auto_id
   )
   UNION (
       SELECT auto_id, address, title, '', description, keyword, rating, `timestamp`
       FROM links
       WHERE user_id = 1 AND (keyword LIKE '%query%')
   )
) AS total
GROUP BY total.auto_id

Answer 2

If you can use the MyISAM table format, try to use a full-text index and search on ta.tag and li.keyword.

Answer 3

Testing this on tables with dozens of rows won't necessarily tell you if there is a performance problem. A DBMS may use different strategies depending on the size of tables.

Try this on larget datasets to get a better assessment of whether there's a problem and just how serious it is.

Answer 4

It is difficult to be sure without the table definitions, but you might be able to rephrase the query as a simpler left join from LINKS to TAGS:

select li.auto_id, 
       address, 
       title, 
       group_concat(ta.tag), 
       description,  
       keyword, 
       rating,  
       timestamp 
from links li  
left join tags ta ON ta.auto_id = li.auto_id  
where li.user_id = 1 and ( keyword like '%query%' or ta.tag like '%query%' ) 
group by li.auto_id;

The logic might need beefing up to cope with nulls in keyword or ta.tag - depending on the table definition.

Answer 5

The % wildcards are probably going to stop your query from being able to use the indexes, particuarly the leading ones - searching for 'cat%' can still use indexes, but '%cat%' can't. Unless your data set is small, that's probably fatal.

I'd also check whether the OR logic is causing you trouble - I'm not sure whether the optimizer will be able to separately optimize the keyword and tag criteria. If it can't, it'll give up and brute-force it.

To re-iterate some of the other comments:

• test with a bigger data set
• try the components of this query first (there's about three separate queries in there) before trying to bolt them all together.