I'm a bit confused about how to optimize my SQL queries. I have a mid-complex query with some joins to run a hundred to thousand times per second (two tables on SSD, one table in RAM, 4 JOINs)
How can I minimize the overhead for execution? Is there any way to precompile a query so that MySQL does not need to analyze, optimize and compile the query each single time?
I know I can use a prepared statement to precompile a query that is then being executed multiple times in the same session. But what to do if you have multiple sessions and only one query per session? Are prepared statements being cached across different sessions? I don't think so.
Then I thought that stored procedures were the best way, because they're said to be precompiled. Now I read that this assumption is totally wrong, they are in fact not precompiled.
Is the any way to share client-sessions in MySQL, e.g. to use prepared statements in the first session inherited by the following sessions?
Last idea is to write a multithreaded socket server to act like a MySQL client-proxy. But that seems a little bit exaggerative to me. ;-)
I use PHP as Apache2 module. Is there any chance to "store" the MySQL session in shared memory, so that the following HTTP requests can use the existing MySQL session instead of starting a new one? So that I can use prepared statements across different HTTP requests?
Q: Is there a way to "re-use" a MySQL connection so that a subsequent request can make use an existing connection?
A: Yes. You can use a connection pool implementation. This is a familiar pattern with Java, with several implementations available.
For a connection pool implementation in PHP, you can use the PHP extension mysqldnd-ms.
Reference: http://php.net/manual/en/mysqlnd-ms.pooling.php
NOTE: I don't have personal experience with this PHP extension.
Some of the other questions you asked...
Q: How can I minimize the overhead for execution? Is there any way to precompile a query so that mysql does not need to analyze, optimize and compile the query each single time?
A: In MySQL 5.6, you can use server side prepared statements. The execution plan for the prepared statement is cached in the session, so a repeated call to the same SQL statement can re-use the previously prepared execution plan. (This feature is not available in MySQL versions before 5.6.)
Reducing the amount of "connection churning** will reduce MySQL overhead. Connecting and disconnecting from the database server is work that the server has to do. It's simple enough to test and compare performance. In one process, open a connection, and do some repeated work (repeated execute a simple statement, like SELECT NOW(), and then disconnect. In another process, run the same repeated executions of the SELECT, but connect and disconnect for each execution.
Q: Are prepared statements being cached across different session?
A: No. Statements are cached at the statement session level.
Q: Is the any way to share client-sessions in mysql, e.g. to use prepared statements in the first session inherited by the following sessions?
A: No. The only way you're going to get that to happen is to not disconnect from the database, and pass the handle of that session to a subsequent client that requests a connection. And we get that to happen by implementing a connection pool.