MySQL Has API to run Multiple Queries at once. This feature was designed mainly with saving network round trip in mind and got a little traction due to associated security risks and not significant gains in most cases.
What would make MySQL Multiple Queries API more usable ?
Allow to run queries in parallel – The great benefit of knowning all queries at once is of course you can run them in parallel. Asynchrnous submission API would be even better but even multi query working this way would be much better. I know there is a work going on on breaking connection=thread=transaction mapping which may allow having multiple working threads for same transaction within same connection which could possibly allow to implement it.
Optimize query set The other thing you can do when you get multiple queries to execute at once you can perform optimizations to share certain execution steps for the queries. This is more niche optimization but it can be very helpful for some applications. On BoardReader for example while performing search we also want to get result search result set, as well as same set grouped by site (to show sites with most matches) as well to group by date to show history of matches per day. This could be very well optimized by performing search query only once while having multiple results aggregation routers building the stream from that result set. We however use Sphinx for this kind of job on BoardReader and Andrew has just implemented such feature.
The other similar case we have is with ClickAider For reporting needs we quite frequently need to have total number of records within the period, number of records with filter applied (ie only clicks from AdSense) and when get the grouped and sorted result set – for example pages with most clicks on them.
Even though second feature may look simple I do not think it would be implemented in MySQL any time soon – this would require very significant optimizer and execution engine changes to optimize for set of queries and to execute query set generating all result set in question at the same time.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.