My findings when developing using large complex sql statements with mysqli in php
The granular approach is more efficient
Sometimes its tempting to execute a large complex sql statements with mysqli to “do it all” in one update but I’ve found this approach to be inefficient in terms of speed and functionality. My advise it to use smaller and precise sql statements instead of large cumbersome queries. The small statements execute quickly and will get the job done faster then the large complex query. For instance in one case I had a complex dynamically generated query with over 230 update statements that updated a single table and concatenated (CONCAT) text to a text field. This query typically hung up and took so long to execute that it caused problems in my ajax environment locking tables and creating conflicts with other operations that were trying to read and write using update and select statements. Once I broke it up into 230 small queries the execution time went down from 30 seconds and failing to 3 to 5 seconds and never failing. In another case when translating csv files to a database I had an issue with a multi query that hung up on large files but once broke it down into running smaller specific queries it was able to execute up to 10000 queries in a few seconds (no I’m not exaggerating it).
In addition to these tidbits here’s some other findings… Mysql lets you set the priority level of queries and by default update queries have a higher priority than select queries so in theory it isn’t possible to run a select on a table that is in the middle of an update transaction if autocommit is being used (it is always on by default). Mysqli locks the table when performing the update and the select has to wait until it is unlocked. This can become cumbersome and unweildy when performing large complex queries and so far I haven’t had very much success with them.
When it comes to complex sql statements with mysqli achieving granularity through smaller interrelated specific sql statements seems to get far greater results for speed and efficiency.
May the source be with you