I have a table with around 300,000 rows. I am trying to count the number of items listed in a primary category (cat1level1) or second category (cat2level1), for example:
SELECT COUNT(*) as num FROM items USE INDEX (cat1level1,cat2level1) WHERE (cat1level1 = '3' OR cat2level1 = '3')
1st and 2nd categories are indexed columns. Category "3" is very popular and has around 200,000 items within it (two thirds of all the items in the table). The count query itself takes around 2-4 seconds to complete when counting items from category "3". Whereas in less-popular categories with only 1000 items, it only takes a more reasonable 0.03 seconds to count.
Does anyone know why this is happening and is it normal? Is there a solution to speed up this count in case this popular category grows to contain a million items?