How to write optimized SQL queries?

Question

Hello colleagues =). Here I read articles about sql requests from experts (about two years ago), but I randomly saw no docking with the article. This concerns query optimization.

The use of "*" in the request is not recommended, which reduces the performance of the request by the server.
made a request:

SELECT * FROM user ... // Выполняется около 0,004 секунд( Проверял тест 10 раз) SELECT `x`, `x1`, `x2`, `xn` FROM user // Выполняется 0,0065 ( В среднем). Примерно полей 14.

And a little question yet. I have one user table. This table is used on several sites that are located on the same server. Each site has its own data which is stored in the user table. But only 30% of the data that is stored in the user table coincide with all projects. And the remaining 70% of each project. The essence of the question: is it worth it to split the user table, as the main + special for each project using LEFT JOIN when querying. Comment on how it will be better in the form of performance?

@Node_pro is not an optimization, it is saving on matches, moreover, at the cost of readability and understanding of what is happening.
Enumeration of columns makes your query more visual, and I can’t believe that this enumeration can become a bottleneck, if I am mistaken, correct it.
Is this why the asterisk is more understandable for the reader of the request than the explicit indication of the columns?
From personal experience - I never disdain replacing the asterisk with an arbitrary field in the subqueries.
This will allow the compiler not to be distracted by drawing up a list of columns
makes sense, only if you are planning to increase the load on the DBMS.
there are utilities that allow you to do load testing for web sites - they will help you find problem areas (for example jmeter)
@renegator - ord, then any person who comes to the project will be forced to climb into the database and dig for a long time and sadly, and why should he bother?

Accepted Answer · 2012-09-10T20:43:02

The problem with asterisks is traffic growth, because all fields are far from always being used, and you have transferred them.

In order to understand the source of the difference in the speed of two queries, you need to look at their execution plans ... There are different things ... From personal experience on http://sql-ex.ru , adding something like in one of the subqueries

 union all select 0

speed increased by 10-20% ...

minamoto minamoto 3,981 9 nineteen · Answer 2 · 2012-09-10T16:36:36

Using an asterisk in a query is not recommended not because of increased performance, but because, using it in a query, you run the risk of subsequently running into a drop in some of your queries / procedures when changing the table.

Request example:

 Insert into table1 (col1, col2, col3) select * from table2

It will stop running if another column is added to table2.

For the second: I see no point in using the left join. If we already combine tables that carry the same meaning, then we need to do this through union all. And whether it is necessary depends on business logic.

And tell me, and in the usual query, is it worth writing the name of the fields or simply "*".
For the second query, for example: SELECT u.*, a.* FROM users as u LEFT JOIN таблица используется только в этом проекте as a ON u.id=a.id
If the request is written for "see what is in the table" and will be deleted afterwards, then it is possible and so.
I have 3 global queries on each page (user, statistics, news).
I stuff this data into an array and use a view, then it’s better * to use?
But I did not understand why the request in your example will cease to be executed?
Well, okay, the insert is still clear, although this problem is solved if the request clearly indicates the columns in which to add information, but with example 2 it is not clear, * it means a sample of all the columns ...
:) An asterisk is not a panacea, you just need to know what you are doing.

Alex Kapustin Alex Kapustin 11.1k 12 nineteen · Answer 3 · 2012-09-10T17:00:41

Pre-optimization - EVIL!

You write as you see fit, and already as a result of testing the application, see what and how much is performed. Keep a log of calls to the database, which query, how many are executed, how many records returned.

On "heavy" queries, do EXPLAIN and add the necessary indexes or write a query on another. Everything comes with experience, immediately learn to write "optimized" requests will not work.

It’s as if I’m not the first day in the Web realm, I always listed as a “book”, but yesterday I looked and was upset =)
@Node_pro And, nevertheless, should still be listed as "on the book."
And performance can be improved by using prepare for repeated requests.
Apparently, some kind of library to access the mysql database.
But the prepare operation is everywhere and for any sql database.
And those languages and / or libraries in which for some reason it suddenly does not exist, just do not need to use for any serious work with sql queries.

an_vital an_vital 135 6 · Answer 4 · 2012-09-11T04:32:07

The speed estimate in this example is not objective. Since the optimizer added your query to the cache when it was first executed, so subsequent executions for this query do not perform such time-consuming operations as query optimization, privilege checking, and other operations specific to queries. running for the first time. Thus, in order to evaluate the speed of query execution, you need to clear the cache, then you can judge which query is executed faster.

I did not clean the cache, but I checked it in turn, and wrote the result above.

How to write optimized SQL queries?

4 answers 4

More articles: