MySQL index on cloudinary_synchronisation table #9
Description
When tracing PHP processes to asses performance overhead, I came across these queries when loading a category page:
16:20:35.668544 write(5, "\211\0\0\0\3SELECT `cloudinary_synchronisation`.* FROM `cloudinary_synchronisation` WHERE (`cloudinary_synchronisation`.`image_name`='hde007_3.jpg')", 141) = 141
16:20:35.672296 write(5, "\211\0\0\0\3SELECT `cloudinary_synchronisation`.* FROM `cloudinary_synchronisation` WHERE (`cloudinary_synchronisation`.`image_name`='hde007_2.jpg')", 141) = 141
16:20:35.674254 write(5, "\211\0\0\0\3SELECT `cloudinary_synchronisation`.* FROM `cloudinary_synchronisation` WHERE (`cloudinary_synchronisation`.`image_name`='hde007_2.jpg')", 141) = 141
... About 50 of those, one per product image in this category I guess. It's not terrible but did add about 0.2 seconds to my page load (not from full page cache).
mysql> EXPLAIN SELECT cloudinary_synchronisation.* FROM cloudinary_synchronisation WHERE (cloudinary_synchronisation.image_name='hde007_2.jpg');
+----+-------------+----------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | cloudinary_synchronisation | ALL | NULL | NULL | NULL | NULL | 1242 | Using where |
+----+-------------+----------------------------+------+---------------+------+---------+------+------+-------------+
It would be good not to trouble the database at all, but we should at least be using an index for these queries. The sample data doesn't have a of images, but the MySQL CPU overhead of this unindexed WHERE is going to start adding up for larger catalogues. Query cache should mostly mitigate this, but there will be cases where query cache is disabled or invalidated for this table, any time it changes.
For now, I'd suggest adding an index on the image_name field when the table is created.
I'll send a pull request on: src/app/code/community/Cloudinary/Cloudinary/sql/cloudinary_setup/install-0.1.0.php
Cheers,
Will.