Get count matches in query on large table very slow
- by Roy Roes
I have a mysql table "items" with 2 integer fields: seid and tiid
The table has about 35000000 records, so it's very large.
seid tiid
-----------
1 1
2 2
2 3
2 4
3 4
4 1
4 2
The table has a primary key on both fields, an index on seid and an index on tiid.
Someone types in 1 or more tiid values and now I would like to get the seid with most results.
For example when someone types 1,2,3, I would like to get seid 2 and 4 as result. They both have 2 matches on the tiid values.
My query so far:
SELECT COUNT(*) as c, seid
FROM items
WHERE tiid IN (1,2,3)
GROUP BY seid
HAVING c = (SELECT COUNT(*) as c, seid
FROM items
WHERE tiid IN (1,2,3)
GROUP BY seid
ORDER BY c DESC
LIMIT 1)
But this query is extremly slow, because of the large table.
Does anyone know how to construct a better query for this purpose?
