Databases
StackOverflow2013
Postsdbo
POWhat is the best way for (potentially) hundreds of mobile clients to access a MySQL database?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is the best way for (potentially) hundreds of mobile clients to access a MySQL database?
    primarykey
    data
    text
    <p>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:</p> <ol> <li><p>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.</p></li> <li><p>The server could potentially have hundreds (or possibly even thousands) of these queries at once.</p></li> <li><p>Since each query runs in its own Thread, each query will at least need its own Statement (and could have its own Connection).</p></li> </ol> <p>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:</p> <p>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.</p> <p>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.</p> <p>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.</p> <p>d) Is it best to use stored SQL procedures for all this?</p> <p>Any hints / comments / suggestions from your experience in these matters are greatly appreciated.</p> <p>EDIT:</p> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USZarjio
    1. USZarjio
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhat is the best way for (potentially) hundreds of mobile clients to access a MySQL database?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhat is the best way for (potentially) hundreds of mobile clients to access a MySQL database?
      1. USSam Holder
      1. VTFavorite
    3. VO
      singulars
      1. POWhat is the best way for (potentially) hundreds of mobile clients to access a MySQL database?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload