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!
I would suggest that you moved out the CLOB column from the table.
I did the same on a project where images was stored as BLOB’s in a table. Since some queries needed a table scan of the table it slowed down things a lot when that large column was part of the table.
As a separate table with just a primary key and the BLOB it speed up the entire application.
Usually caching is handled in blocks so it has to operate on the entire records not just the columns that you repeatedly select.
But some storage engines handles BLOB/CLOB’s differently so I can’t say for certain.
AFAIK, InnoDB buffer pool “caches” entire innodb pages (16kb blocks of records/indexes) and it does not work with records/fields on this level. So, if you read one field from one row in some page, it would read an entire page from the disk and then would store it in buffer pool.
[B]scoundrel wrote on Thu, 20 September 2007 19:46[/B]
AFAIK, InnoDB buffer pool "caches" entire innodb pages (16kb blocks of records/indexes) and it does not work with records/fields on this level. So, if you read one field from one row in some page, it would read an entire page from the disk and then would store it in buffer pool.
That’s what I though so I made a new table, with itemid as a primary key and description. And I’m storing the descriptions compressed (takes about 1/4 the size). That seems to be working fine but it doesn’t seem to have really sped up my products table yet. However, I think that’s because I didn’t drop the description field, I just set them all to blank. My partition where mysql’s data is, is only 12 GB, and 11 GB are used. So I can’t optimize the table (or alter it) (the diskspace would fill up and crash!). So it’s probably fragmented a lot now. The only think I can think of is to export all the innodb tables, shutdown mysql remove the innodb data file, start back up and import the data again. I’ve done this once before and it wasn’t fun and took me from midnight to 8am. I’d rather not do that, but if I have to I will. Any other suggestions?