SQL Optimization – One large versus many small queries




In the recent past we have rewritten several important pieces of code in Tweakers. Earlier this year our new comment system introduced in April and May, we have been busy rewriting the karma system.

It is not only dealt with the functionality, but also the performance of the components are re-examined. In both systems, the performance in the use of the dominant database for the total time required. A good shape in order to save the data, with the appropriate indices, and efficiently read out the data are therefore very important. For both examples, we use our central MySQL database.

On the internet you can find all sorts of statements about the speed of SQL databases. In recent years, for example, appeared all kinds of NoSQL databases, and often one or more negative aspects which are to go better with such a new database. It is often argued that meet all four components of acid the performance and scalability of the way. It is especially consistency and isolationism weaker developed in those systems.

Also, the complex queries that are possible with SQL, are referred to; that possibility would be bad for performance and moreover are not needed at all. It can join -and tables and opportunities for subqueries are hard examples that. It even goes so far as is recommended in one breath to avoid those things even if you do indeed continue to use SQL. It would also be bad to unite performance and tricky with sharding functionality. The advice is therefore to the queries to keep as simple as possible and preferably only search through the primary key in a table. Complex operations such as the combination of data, may finally also be done in the application layer.

If you use an object relational mapper such as Doctrine, then that advice are effective standard followed. In an orm should usually be done even bother to deviate from that model. Using a join often means adding extra code.

Although it is always wise to keep simple code, it is sin therefore remain large performance gains. The opinions on sharding data are finally relevant only if it is actually used. If no sharding is used, it is just convenient to link data from different queries in the database already. That can quite care what overhead; think of the single round-trips of query and data, legal checks and all related actions in the database less often have to be done.

There is, of course a difference between the various platforms. On some platforms, including many types of applications in Java application servers, can orm ‘S data in the memory of the application cache. In such cases, round-trips are already saved, but it stands or falls with the effective than hitratio of the cache and the quality of the synchronization with the database. The advice given here are valid especially for applications where these caches are not practical, as is usually the case with PHP.

For us is the database of Tweakers with 219GB way, not in the order to move on sharding or other techniques to use several servers simultaneously. Moreover, we do have a number of ways to ensure that the MySQL database need not be used for all data. For example, we chose to certain pieces of data in MongoDB to store or in memcached caching and much of the information is through our Java environment called.

In this paper we describe two applications where database performance were important to us. It let gradually see some optimizations that gave signficante improvements in performance. Note that this article is not about placing the proper indices or optimisaliseren the settings of a database. This is a parallel task which is examined whether the database will be used optimally, but a well-optimized table structure and database are of course also important for good performance.

Next page
Queries for comments
Table of contents

1. Introduction
2. Queries for comments
3. Batch Queries for comments
4. Karma: simple calculation
5. Karma: more complex calculations
6. Conclusion
Reactions (45)


In: A Technology & Gadgets Asked By: [19102 Red Star Level]

Answer this Question

You must be Logged In to post an Answer.

Not a member yet? Sign Up Now »

Star Points Scale

Earn points for Asking and Answering Questions!

Grey Sta Levelr [1 - 25 Grey Star Level]
Green Star Level [26 - 50 Green Star Level]
Blue Star Level [51 - 500 Blue Star Level]
Orange Star Level [501 - 5000 Orange Star Level]
Red Star Level [5001 - 25000 Red Star Level]
Black Star Level [25001+ Black Star Level]