I’m facing a similar problem to the original poster’s, perhaps you can help.
The problim is: I have items that belong to a certain category. You can select which categories to view, and browse through these items by page.
For example, you might view page 1000 of categories 1, 2, 10, and 30.
Sample table:
CREATE TABLE test
( id
int(10) unsigned NOT NULL auto_increment, category
tinyint(3) unsigned NOT NULL, PRIMARY KEY (id
), KEY cat
(category
)) ENGINE=MyISAM
Sample query to view page 1000 of cateogies 1,2,10,30:
SELECT id FROM test WHERE category IN(1,2,10,30) ORDER BY id DESC LIMIT 10000,10
I have the ORDER BY id DESC because the latest items are added at the end, so page 1 would be the last rows of the table.
I don’t know how to optimize this query - as you said, this kind of query is generating 10,010 rows and throwing away all but 10 of them.
There will ultimately be millions of items, but not hundreds of millions (but there will potentially be 100,000 added daily). There will be less than 200 categories, and the user will need to be able to select any categories they want and browse through the pages.
Any ideas?