Replication of database query with multiple FIND_IN_SET ()

Question

Replication of database query with multiple FIND_IN_SET ()

Navigation

#1 by (2 votes)

3

I have the following query to find topics related 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 according to the example above is receiving two ID's, but if it gets twenty, it will become a bit repetitive.

In addition, the , but is in this example used to find values separated by ; .

Question

How to optimize the query in order to ensure the correct performance of the query as the table fills up and / or the number of IDs to be located is increasing?

-- ...
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 assist with testing, with minimal structure for example in given.

mysql sql-select mysql-find-in-set

asked by anonymous 03.07.2014 / 03:16

1 answer

UTF-8: reading and inserting into MySQL via Ruby 1.8 External Connection JSON WebService

score 2 · Answer 1

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

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

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

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

Then the whole block with FIND_IN_SET can 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 id (PK with auto increment) column, some frameworks require this to be able to delete rows.