Here is my problem, I have a table for products which is about 1,000,000 rows, and 5GB. Some queries (especially with ORDER by) take a very long time, and then go quickly when called again for a while. This happens even with query cache off. My best guess so far as to why this is, is that the first time I run a query the data is not in the INNOdb buffer, but subsequent queries run faster because then the data is in the buffer.
Currently my buffer is set to 2GB. I thought that if my queries never use a field, such as description, then that field would not be with the row in the buffer, like in the query cache. However, if all of the data of the row is put in the buffer, regardless of what fields you use, that would explain my problem.
Can anyone confirm if all the data for a row goes in the innodb buffer, or just the fields you are using?
If it is all the data, then I imagine I could speed things up by putting the description field in a separate table, because this field can be 1KBytes - 4KBytes in size. It’s only used when someone on my site clicks on a product. In the results list it’s never used. So assuming that the innodb buffer pool puts all the data in the buffer, if I remove all the descriptions the table should be significantly smaller and fit in the buffer, and queries would go faster…
Thank You for your help I’ve been trying to figure this out for days!
-Mark