Sql multiple queries in one. Multiple SELECT COUNTs in one MySQL query

In the last lesson, we encountered one inconvenience. When we wanted to know who created the "bicycles" topic, and made a corresponding query:

Instead of the author's name, we received his identifier. This is understandable, because we made a query to one table - Topics, and the names of topic authors are stored in another table - Users. Therefore, having learned the identifier of the theme author, we need to make one more query - to the Users table to find out his name:

SQL provides the ability to combine such queries into one by turning one of them into a subquery (nested query). So, to find out who created the "bicycles" topic, we'll make the following query:

That is, after the keyword WHERE, we write one more query in the condition. MySQL processes the subquery first, returns id_author=2 and this value is passed to the clause WHERE external request.

There can be several subqueries in one query, the syntax for such a query is as follows: Note that subqueries can select only one column, the values ​​of which they will return to the outer query. Attempting to select multiple columns will result in an error.

Let's make another request to consolidate, find out what messages the author of the "bicycles" topic left on the forum:

Now let's complicate the task, find out in which topics the author of the "bicycles" topic left messages:

Let's see how it works.

• MySQL will execute the deepest query first:


• The result (id_author=2) will be passed to external request, which will take the form:


• The result (id_topic:4,1) will be passed to an external query, which will take the form:


• And it will give the final result (topic_name: about fishing, about fishing). Those. the author of the topic "bicycles" posted messages in the topic "About fishing" created by Sergey (id=1) and in the topic "About fishing" created by Sveta (id=4).

That's actually all that I wanted to say about nested queries. However, there are two points to pay attention to:

• It is not recommended to create queries with a nesting level greater than three. This leads to an increase in execution time and to the complexity of the perception of the code.
• The given syntax of nested queries is rather the most common, but by no means the only one. For example, instead of requesting
  
  write
  
  Those. we can use any operators used with keyword WHERE (we studied them in the last lesson).

October 9, 2008 at 11:37 pm

MySQL query optimization

• MySQL

In everyday work, you have to deal with fairly similar errors when writing queries.

In this article, I would like to give examples of how NOT to write queries.

• Selecting all fields
  SELECT * FROM table

  When writing queries, do not select all fields - "*". List only the fields you really need. This will reduce the amount of data fetched and sent. Also, don't forget about covering indexes. Even if you really need all the fields in the table, it's best to list them. First, it improves the readability of the code. When using an asterisk, it is impossible to find out what fields are in the table without looking into it. Secondly, over time, the number of columns in your table may change, and if today it is five INT columns, then in a month TEXT and BLOB fields may be added, which will slow down the selection.

• Requests in a cycle.
  You need to clearly understand that SQL is a language that operates on sets. Sometimes programmers who are accustomed to thinking in terms of procedural languages ​​find it difficult to restructure their thinking in the language of sets. This can be done quite simply by adopting a simple rule - "never execute queries in a loop." Examples of how this can be done:

  1. Samples
  $news_ids = get_list("SELECT news_id FROM today_news ");
  while($news_id = get_next($news_ids))
  $news = get_row("SELECT title, body FROM news WHERE news_id = ". $news_id);

  The rule is very simple - the fewer requests, the better (although there are exceptions to this, as with any rule). Don't forget the IN() construct. The above code can be written in one query:
  SELECT title, body FROM today_news INNER JOIN news USING(news_id)

  2. Inserts
  $log = parse_log();
  while($record = next($log))
  query("INSERT INTO logs SET value = "(!LANG:. $log["value"]);!}

  It's much more efficient to glue and execute a single query:
  INSERT INTO logs (value) VALUES (...), (...)

  3. Updates
  Sometimes you need to update multiple rows in the same table. If the updated value is the same, then everything is simple:
  UPDATE news SET title="(!LANG:test" WHERE id IN (1, 2, 3).!}

  If the value to change for each entry is different, then this can be done with the following query:
  UPDATE news SET
  title = CASE
  WHEN news_id = 1 THEN "aa"
  WHEN news_id = 2 THEN "bb" END
  WHERE news_id IN (1, 2)

  Our tests show that such a query is 2-3 times faster than several separate queries.

• Performing operations on indexed fields
  SELECT user_id FROM users WHERE blogs_count * 2 = $value

  This query will not use an index even if the blogs_count column is indexed. In order for an index to be used, no transformations must be performed on the indexed field in the query. For such requests, move the conversion functions to another part:
  SELECT user_id FROM users WHERE blogs_count = $value / 2;

  Similar example:
  SELECT user_id FROM users WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(registered)<= 10;

  Will not use an index on the registered field, while
  SELECT user_id FROM users WHERE registered >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
  will be.

• Fetching rows just to count their number
  $result = mysql_query("SELECT * FROM table", $link);
  $num_rows = mysql_num_rows($result);
  If you need to select the number of rows that meet a certain condition, use SELECT query COUNT(*) FROM table rather than select all rows just to count them.
• Fetching Extra Rows
  $result = mysql_query("SELECT * FROM table1", $link);
  while($row = mysql_fetch_assoc($result) && $i< 20) {
  …
  }
  If you only want n sample rows, use LIMIT instead of discarding extra rows in your application.
• Using ORDER BY RAND()
  SELECT * FROM table ORDER BY RAND() LIMIT 1;

  If the table has more than 4-5 thousand rows, then ORDER BY RAND () will work very slowly. It will be much more efficient to run two queries:

  If the table has an auto_increment" primary key and no gaps:
  $rnd = rand(1, query("SELECT MAX(id) FROM table"));
  $row = query("SELECT * FROM table WHERE id = ".$rnd);

  Or:
  $cnt = query("SELECT COUNT(*) FROM table");
  $row = query("SELECT * FROM table LIMIT ".$cnt.", 1");
  which, however, can also be slow with a very large number of rows in the table.

• Usage a large number JOIN"s
  SELECT
  v.video_id
  a.name,
  g.genre
  FROM
  videos AS v
  LEFT JOIN
  link_actors_videos AS la ON la.video_id = v.video_id
  LEFT JOIN
  actors AS a ON a.actor_id = la.actor_id
  LEFT JOIN
  link_genre_video AS lg ON lg.video_id = v.video_id
  LEFT JOIN
  genres AS g ON g.genre_id = lg.genre_id

  It must be remembered that when tables are linked one-to-many, the number of rows in the selection will grow with each next JOIN "e. For such cases, it is faster to break such a query into several simple ones.

• Using LIMIT
  SELECT... FROM table LIMIT $start, $per_page

  Many people think that such a query will return $per_page records (usually 10-20) and therefore work quickly. It will work quickly for the first few pages. But if the number of records is large and you need to execute a query SELECT ... FROM table LIMIT 1000000, 1000020, then to execute such a query, MySQL will first select 1000020 records, discard the first million and return 20. This may not be fast at all. There are no trivial ways to solve the problem. Many simply limit the number of available pages to a reasonable number. It is also possible to speed up such queries by using covering indexes or third party solutions (eg sphinx).

• Not using ON DUPLICATE KEY UPDATE
  $row = query("SELECT * FROM table WHERE id=1");

  If($row)
  query("UPDATE table SET column = column + 1 WHERE id=1")
  else
  query("INSERT INTO table SET column = 1, id=1");

  A similar construction can be replaced with a single request, provided that there is a primary or unique key in the id field:
  INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1

Read

This short article will focus on databases in particular MySQL, sampling and counting. Working with databases, it is often necessary to count the number of COUNT() rows with or without a certain condition, this is extremely easy to do with the following query

Viewing MySQL Code

The query will return a value, with the number of rows in the table.

Conditional counting

Viewing MySQL Code

The query will return a value with the number of rows in the table that satisfy the given condition: var = 1

To get multiple row count values ​​with different conditions, you can execute multiple queries one by one, for example

Viewing MySQL Code

But in some cases, this approach is not practical and not optimal. Therefore, the organization of a query becomes relevant, with several subqueries, in order to obtain several results in one query at once. For example

Viewing MySQL Code

Thus, by executing just one query to the database, we get a result with counting the number of rows according to several conditions, containing several count values, for example

View code TEXT

c1|c2|c3 -------- 1 |5 |8

The disadvantage of using subqueries, compared to several separate queries, can be considered the speed of execution and the load on the database.

The following example of a query containing multiple COUNTs in a single MySQL query is structured slightly differently, using IF(condition, value1, value2) constructs, as well as SUM() summation. Allowing you to select data according to specified criteria within a single query, then summarize them, and display several values ​​as a result.

Viewing MySQL Code

As you can see from the query, it is built quite concisely, but the speed of its execution was also not pleasing, the result of this query will be the following,

View code TEXT

total|c1|c2|c3 -------------- 14 |1 |5 |8

Next, I will give comparative statistics on the speed of execution of three query options for selecting several COUNT () . To test the speed of query execution, 1000 queries of each type were executed, with a table containing more than three thousand records. At the same time, each time the query contained SQL_NO_CACHE to disable caching of results by the database.

Execution speed
Three separate requests: 0.9 sec
One query with subqueries: 0.95 sec
One query with IF and SUM construct: 1.5 sec

Conclusion. And so, we have several options for building queries to the database MySQL data with multiple COUNT(), the first option with separate queries is not very convenient, but has the best result in terms of speed. The second option with subqueries is somewhat more convenient, but its execution speed is slightly lower. And finally, the third concise version of the query with the IF and SUM constructs, which seems to be the most convenient, has the most low speed performance, which is almost two times lower than the first two options. Therefore, when optimizing the operation of the database, I recommend using the second query option containing subqueries with COUNT (), firstly, its execution speed is close to the fastest result, and secondly, such an organization within a single query is quite convenient.