October 22, 2014

What would make MySQL Multiple Queries Usable ?

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.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Andrisi says:

    What you suggest with the optimization thing is related to a fundamental weakness of SQL: that it can return only one dataset per query, even tough you sometimes need more than one related datasets, datasets that could be generated at once. Returning multiple datasets for one query would help the server too – in exactly the same way as you’re suggesting above. And maybe that would be easier to implement than finding similr parts in queries submitted in a batch.

    See: http://forge.mysql.com/worklog/task.php?id=358 (by me)

Speak Your Mind

*