
some help with optimization
okay here's my deal.
i'm the programmer for a site that lets people keep track of items they have on this pet site, subeta. there are almost 20,000 items total on the site.
people can make checklists for pretty much anything, based on things like item type or shop or whatever. they can also have a checklist for just every item, as subeta has an achievement for having (i think it goes up to 15,000) lots of items in a gallery on their site.
so here's where i start having a problem. there are pages where people can see what's in their collection and what's missing. this becomes an issue with the queries, because the only way i can think of to do those queries is to use a WHERE...IN for with the item id list.
is there a good way to do this without using a WHERE...IN select statement? because when there are a few thousand item ids in that list, it's screwing with the server load. our account has been suspended by our host twice today due to higher traffic (from subeta starting a new event this afternoon) on these inefficient queries.
any ideas?