CODEWITHSUNDEEP
mysqlsql
meder omuraliev
meder omuraliev

Reputation: 186652

Optimize SQL statement with multiple CONCAT clauses?

SELECT
*

FROM
pages


INNER JOIN cms_collection ON cms_collection.collection_id LIKE 

( 
   CONCAT('%', pages.page_id, '/heading%') OR
   CONCAT('%', pages.page_id, '/content%') 
)

WHERE
site_id = 51

LIMIT 10

The stacked CONCATs are causing the query to be super slow, is there anyway to optimize this? And actually it seems like this statement isn't working as expected.. I want to do a LIKE based on if the collection_id is '%pages.page_id/heading%' OR '%pages.page_id/content%'.

This statement works fine if just the first CONCAT exists.

Upvotes: 0

Views: 115

Answers (2)

Tav
Tav

Reputation: 340

SELECT * FROM pages
INNER JOIN cms_collection ON
(cms_collection.collection_id LIKE CONCAT('%', pages.page_id, '/heading%'))
WHERE site_id = 51

UNION ALL

SELECT * FROM pages
INNER JOIN cms_collection ON
(cms_collection.collection_id LIKE CONCAT('%', pages.page_id, '/content%'))
WHERE site_id = 51 
LIMIT 10
;

Using UNION ALL instead of OR improves performance

Upvotes: 0

Dark Falcon
Dark Falcon

Reputation: 44191

OR is a logical operator. It does not define alternatives for LIKE. You need to split that out into two LIKE clauses.

SELECT * FROM pages
INNER JOIN cms_collection ON
(cms_collection.collection_id LIKE CONCAT('%', pages.page_id, '/heading%')) OR
(cms_collection.collection_id LIKE CONCAT('%', pages.page_id, '/content%'))
WHERE site_id = 51 LIMIT 10

If that is still slow, add your table structure and the output of an EXPLAIN to your question.

Upvotes: 2

Related Questions