Clever Geek Handbook

Is MySQL a faster method for this complex query?

Is there a more resource-intensive / faster way to fulfill this query (based in part on: https://stackoverflow.com/a/2504572/2504572/2504572/2504572/ ). It currently takes 0.008 seconds, looking at only about a dozen rows in the 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

Many thanks,

Ice

0


source to share


5 answers


I would like to hope that the query optimizer does this for you, but you can try making a selection on the user_id tags before doing the join just in case in the first subquery. This would reduce the number of lines you would have to join, presumably. You probably also want to have indexes for 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
+2


source


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



+1


source


Testing this on tables with dozens of rows will not necessarily tell you if there is a performance issue. The DBMS can use different strategies depending on the size of the tables.

Try this on large datasets to better assess if the problem is and how serious it is.

+1


source


It's hard to be sure without defining tables, but you can 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 may need amplification to deal with zeroes in the keyword or ta.tag, depending on the table definition.

0


source


The% wildcards are likely to stop your query from being able to use indexes, especially leading ones - "cat%" searches can still use indexes, but "% cat%" cannot. If your dataset is small, it may be fatal.

I would also check if the logic is OR causing problems - I'm not sure if the optimizer can optimize the keyword and tag criteria separately. If he cannot, he will give up and force him rudely.

To reiterate some of the other comments:

  • with a large dataset
  • try the components of this query first (there are about three separate queries) before trying to put them all together.
0


source






More articles:


All Articles