MySQL: Limit for WHERE IN List

Question

Say there are millions of records in my table.

Here is my query to retrieve strings with a specific name from a list:

SELECT * FROM my_table WHERE Name IN ('name1','name2','name3','name4')

How to limit the returned result to name1, name2, etc.? The following query would limit the entire result (to 100).

SELECT * FROM my_table WHERE Name IN ('name1','name2','name3','name4') LIMIT 100

I need to limit to 100 for each name.

Answer 1

This is a bit of a pain in MySQL, but the best method is probably the variables:

select t.*
from (select t.*,
             (@rn := if(@n = name, @rn + 1,
                        if(@n := name, 1, 1)
                       )
             ) as rn
      from my_table t cross join
           (select @n := '', @rn := 0) params
      order by name
     ) t
where rn <= 100;

If you want to limit this to a subset of names, then add the sentence whereto the subquery.

. - , - order by .

Answer 2

Try SELECT * FROM my_table IN ('name1', 'name2', 'name3', 'name4') FETCH FIRST 100 ROWS ONLY