Mysql BLOB performance degradation after hitting "a limit" (MyISAM)
I have a rather small mysql DB in which I store an handful of pretty small images (320x480), it all behaved really well until recently, the table size is about 1.5Gb and there are about 34,000 records. At that point...
...All of a sudden the performance degradated by a factor of 100.
It looks like the DB has hit a rock hard limit and it can't cope with it anymore.
Anyone experienced anything like it?
(Please don't suggest to move the images off the DB on the filesystem, we have dec开发者_Go百科ided to do that with the next version of the software)
Possible reason is that your query is not using indexes. When table was rather small, it was not a problem, but a bigger it gets, the tougher it is for MySQL to get your data.
Check if column property_id is indexed. To speed up your query you can add covering index - (property_id
, image
)
You can see if your query uses indexes and more useful info by adding EXPLAIN
to your query:
EXPLAIN SELECT image FROM mytable WHERE property_id = 30000
I am quite certain that the database has started utilizing the disk instead of memory to do an operation. This operation could very well be to store data to temporary tables. Store indexes on disk in instead etc. Figure out what it is, and you solution will be a lot simpler to find.
The performance degration of 1 to 100 would fit with the speed difference between memory and disk.
Do not create an index on the BLOB field, it is useless (and MySQL won't allow it).
Do you have proper indexes ? ie in this case, (property_id) ?...
精彩评论