How to rewrite the slow WHERE IN query

advertisements

I am not very familiar with mysql and was wondering how I might rewrite the following query to fix it and speed it up. I believe I'd have to use a JOIN (or something else), but am not sure how to do this.

What I want to achieve is to perform a simple MATCH AGAINST query, and using the IDs from these results (in table2), retrieve all corresponding rows with the same IDs in table1.

SELECT *
FROM table1
WHERE id IN (
    SELECT id
    FROM table2
    WHERE MATCH (gloss) AGAINST ('example' IN BOOLEAN MODE)
    LIMIT 10
)

Note that I'm aware the subquery above doesn't work with a LIMIT.

Thank you for your time.


try a simple inner join

SELECT *
FROM table1
INNER JOIN table2
USING(id)
WHERE MATCH (gloss) AGAINST ('example' IN BOOLEAN MODE)
LIMIT 10

to run your IN with a LIMIT you have to wrap it inside another query like this

SELECT *
FROM table1
WHERE id IN (SELECT id FROM
                 (SELECT id
                  FROM table2
                  WHERE MATCH (gloss) AGAINST ('example' IN BOOLEAN MODE)
                  LIMIT 10
                 )T1
            )

to get exact same result as your IN with LIMIT 10 using INNER JOIN you can do this

SELECT *
FROM table1
INNER JOIN
 (SELECT id
  FROM table2
  WHERE MATCH (gloss) AGAINST ('example' IN BOOLEAN MODE)
  LIMIT 10
 )T1
USING (id)