Database query refactoring with multiples FIND_IN_SET()

Asked

Viewed 157 times

3

I have the following query to find related topics by a set of ID’s that are not the topic to be viewed:

SELECT
    press.image,
    press_i18n.title,
    press_i18n.slug
FROM press
INNER JOIN press_i18n ON (
    press.press_id = press_i18n.press_id
)
WHERE press.ocult_from_site = 'no'
AND press.status = 'active'
AND press_i18n.i18n_id = 'por'
AND (
    FIND_IN_SET (1326, REPLACE(press.tag_id, ";", ","))
    OR
    FIND_IN_SET (77, REPLACE(press.tag_id, ";", ","))
)
AND press_i18n.slug != 'bubu-tem-muito-sono'
ORDER by press.publish_date DESC

Showing Rows 0 - 7 (8 total, Query Took 0.0047 sec)

The query as the example above is receiving two ID’s, but if you receive twenty, it will become a little repetitive.

In addition, the function FIND_IN_SET() was designed to work with , but is being used in this example to locate values separated by ;.

Question

How to optimize the query to ensure the correct performance of the same as the table fills and/ or the number of ID’s to locate increases?

-- ...
AND (
    FIND_IN_SET (1326, REPLACE(press.tag_id, ";", ","))
    OR
    FIND_IN_SET (77, REPLACE(press.tag_id, ";", ","))
    OR
    FIND_IN_SET (10545, REPLACE(press.tag_id, ";", ","))
    OR
    FIND_IN_SET (43256, REPLACE(press.tag_id, ";", ","))
    OR
    FIND_IN_SET (1234567, REPLACE(press.tag_id, ";", ","))
    OR
    FIND_IN_SET (7654321, REPLACE(press.tag_id, ";", ","))
)
-- ...

SQL Fiddle to help test, with the minimum structure for the given example.

mysql select-sql refactoring mysql-find-in-set

  • It is worth to touch the structure of the bank, or only the query?

  • Anything goes, optimized structure and/or optimized query, the important thing is to ensure the performance over time. @bfavaretto

1 answer

2

It would be interesting to normalize the bank, creating a relationship table between press and tags. The column press.tag_id would be eliminated, and the relationship table press_tags would look this way:

press_id   tag_id
------------------
       1     1326
       1       77
       1    10545

You make a JOIN with this table in the query body:

LEFT OUTER JOIN press_tags
ON press_tag.press_id = press.id

There the whole block with the FIND_IN_SET may be replaced by:

AND press_tags.tag_id IN(1326, 77, 10545)

All together:

SELECT
    press.image,
    press_i18n.title,
    press_i18n.slug
FROM press
INNER JOIN press_i18n ON (
    press.press_id = press_i18n.press_id
)
LEFT OUTER JOIN press_tags ON (
    press_tag.press_id = press.id
    -- ou 
    -- press_tag.press_id = press.press_id
    -- (o que fizer mais sentido no seu banco)
)
WHERE press.ocult_from_site = 'no'
AND press.status = 'active'
AND press_i18n.i18n_id = 'por'
AND press_tags.tag_id IN(1326, 77, 10545)
AND press_i18n.slug != 'bubu-tem-muito-sono'
ORDER by press.publish_date DESC

You should also need an index on tag_id in this relationship table. You may also want a column id (PK with auto increment), some frameworks require this in order to delete lines.

Browser other questions tagged mysql select-sql refactoring mysql-find-in-set

You are not signed in. Login or sign up in order to post.