Question:

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 receives twenty, it will become a little repetitive.

Also, the FIND_IN_SET() function was designed to work with , but is being used in this example to find values ​​separated by ; .

Question

How to optimize the query in order to guarantee its correct performance as the table fills and/or the number of ID's to be located 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 with testing, with minimal structure for the given example.

Answer:

It would be interesting for you to normalize the database, creating a relationship table between press and tags . The press.tag_id column would be press.tag_id , 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 query body:

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

Then the entire block with the 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 might also need an index on tag_id this relationship table. You may also want an id column (PK with auto increment), some frameworks require this to be able to delete rows.