Optimize SQL statement with multiple CONCAT clauses?

Question

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.

Dark Falcon · Accepted Answer · 2013-06-11 20:37:43Z

2

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.

answered Jun 11, 2013 at 20:37

Dark Falcon

44.2k6 gold badges91 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

davids · Accepted Answer · 2013-08-23 13:23:53Z

0

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

edited Aug 23, 2013 at 13:23

davids

5,60712 gold badges60 silver badges96 bronze badges

answered Aug 23, 2013 at 13:04

Tav

3402 silver badges8 bronze badges

1 Comment

Andy Lester Over a year ago

I can't imagine that's faster than doing an OR in the WHERE clause.

Collectives™ on Stack Overflow

Optimize SQL statement with multiple CONCAT clauses?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related