Been disappointed with MySQL prepared statement test, I decided to write an article about old and proven approaches in MySQL speed improvement. Most of described in this article might be similar to the reader (and I clearly understand this), but decided to describe this anyway as far as I am going to write several articles about PHP and Erlang implementations of this approaches. In this particular article I am going just briefly describe SQL statements that can be used to improve MySQL productivity and reduce server load. So, this article is going to be little bit theoretical without PHP or Erlang code, but follow my blog and I hope very soon I will publish more code.
Almost anybody who have ever made a database backup should be familar with this SQL statement.
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6);
One might think that this shorter statement is used just to simplify the dump file and make it shorter, but it is also much faster in execution than several ordinary insert statements.
Implementation ofthis insert in a project will require some in memory caching and one should remember that cached sensetive data can be loast during server failure (for example power failure). Sure this can be prevented in many different ways, but it is just very important thing to remember.
MySQL does not allow bulk updates directly, but can be implemented through slightly extended MySQL insert statement. This one is so widely used and probably not so familar, but is extremelly in some cases.
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
I think this statement is quite clear and does not require a lot of explanation it isvery similar to the bulk insert, but table should have unique primary key. Usually this is not a problem, as most of the tables has it.
One nay notice that all selects can be named "bulk selects", but I would like to talk here about the type of requests that are executed to retrieve just one record. For example this may be an article by ID, or video by ID. If you will have huge traffic to your site you may get huge MySQL server load. Simple and extremely efficient way to reduce the load is to split the requests in a following way:
SELECT * FROM atricles WHERE ID IN (1,2,3,4)
Result may be cached in some key-value storage like Memcached and reduce MySQL and overall server load. Sounds pretty simple, but might be not so simple in implementation. I am going to describe this case in one of my future articles as soon as I will get some free time and little bit of inspiration
Bulk delete statement is very similar to the select one. It can be useful to remove several records at once and avoid table blocking (if we are talking about MyISAM tables).
DELETE FROM atricles WHERE ID IN (1,2,3,4)
Again it is very important to remember that cache data can be lost during server failure and records can stay in the database.
I am not going to write test code here and compare the speed of bulk statements with the sets of ordinary statements. I think there should be some tests available in the net regarding this subject, as this is not something new. But you may let me know if you are interrested in such tests.