Re: Slow query, trying to optimize search..
Okay!
So I am learning the joys of indexing, your advice is very helpful!
I had the query cache turned off for the longest time, then in my expirementation I turned it back on a couple of days ago and it is seems to be working to some degree. No complaints on the speed when it is properly using it's index.
Once mysql has been running for a day (I just restarted it now) I will post a SHOW STATUS but here is a line from mysqltuner from yesterday with 24 hours of runtime:
Query cache efficiency: 62.9% (501K cached / 797K selects)
I am also beginning to see that I should use PHP to restructure the query itself if the user is specifying one of the different modes. By default, my site does it all backwards by specifying the status_int first, if no other column is specified. I need to change that around so the most specific thing is first!
So now the question remains on a multi column index on the remaining unspecific columns. Or to add a timestamp index. I could imagine that having an index on the timestamp column and then asking it to select where timestamp is less than a certain amount, like the last month, it will reduce the size of the dataset and will make the entire query much faster. But I would still want the COUNT(*) for that entire datase, and if I asked for the reduced dataset (WHERE timestmap is a certain range from now) it will give me a reduced count.. I basically want my cake and eat it too. Was thinking about it today though, and I could possibly just re-query the numbers and display a cached result stored in a tempory table updated with a cron script. But I am sure there is a better way.
I'm sort of asking for the impossible, that's why it's confusing.
SELECT * FROM subjects WHERE NOT status_int = '0' AND site_filter = '1' ORDER BY view_count ASC LIMIT 0,100;
That's what I am using now...
But status_int is the least important thing in the world. Also site_filter is very important but not really specific in any way, will be a higher piority than status though (which is 0 or 1) where filter can be one of five single digit numbers. Not much to sort through, but if it can reduce the dataset to 20% and do an query on that it would certainly speed things up.
Also doing 'EXPLAIN' on queries is extremely useful!
So your:
INDEX(name, status_int, site_filter)
or I might as well correct (myself):
INDEX(name, site_filter, status_int)
This index will be used if I am only specifying site_filter = 2 and status_int = 1? I think that is what I will end up using then.
Is there a different way to handle a situation like mine though? Where you have a massive dataset and want to query against it to get 90% of the entire dataset quickly but sorted by any column in particular?