What is the best way for (potentially) hundreds of mobile clients to access a MySQL database?

Question

So, here is the deal. I'm developing an Android application (although it could just as easily be any other mobile platform) that will occasionally be sending queries to a server (which is written is Java). This server will then search a MySQL database for the query, and send the results back to the Android. Although this sounds fairly generic, here are some specifics:

1. The Android will make a new TCP connection to the server every time it queries. The server is geographically close, the Android could potentially be moving around a lot, and, since the Android app might run for hours while only sending a few queries, this seemed the best use of resources.

2. The server could potentially have hundreds (or possibly even thousands) of these queries at once.

3. Since each query runs in its own Thread, each query will at least need its own Statement (and could have its own Connection).

Right now, the server is set up to make one Connection to the database, and then create a new Statement for each query. My questions for those of you with some database experience (MySQL in particular, since it is a MySQL database) are:

a) Is it thread safe to create one Statement per Thread from a single Connection? From what I understand it is, just looking for confirmation.

b) Is there any thread safe way for multiple threads to use a single PreparedStatement? These queries will all be pretty much identical, and since each thread will execute only one query and then return, this would be ideal.

c) Should I be creating a new Connection for each Thread, or is it better to spawn new Statements from a single Connection? I think a single Connection would be better performance-wise, but I have no idea what the overhead for establishing a DB Connection is.

d) Is it best to use stored SQL procedures for all this?

Any hints / comments / suggestions from your experience in these matters are greatly appreciated.

EDIT:

Just to clarify, the android sends queries over the network to the server, which then queries the database. The android does not directly communicate with the database. I am mainly wondering about best practices for the server-database connection here.

Answer 1

Just because a Connection object is thread safe does not mean its thread efficient. You should use a Connection pool as a best practice to avoid potential blocking issues. But in answer to your question, yes you can share a Connection object between multiple threads.

You do need to create a new Statements/Prepared Statements in each thread that will be accessing the database, they are NOT thread safe. I would highly recommend using Prepared Statements as you will gain efficiency and protection against SQL injection attacks.

Stored procedures will speed up your database queries since the execution plan is compiled already and saved - highly recommended to use if you can.

Have you looked at caching your database data? Take a look at spymemcached if you can, its a great product for reducing number of calls to your data store.