MySQL Forums
Forum List  »  PHP

Re: Complex queries
Posted by: Rick James
Date: May 27, 2014 02:55PM

Shailendu, not always. Here's an example where subqueries is slower. In this example, the aggregates are 3x faster:

mysql> SELECT
    -> SUM(LENGTH(city) < 5 ) AS Under5,
    -> SUM(LENGTH(city) = 5 ) AS Len5,
    -> SUM(LENGTH(city) = 6 ) AS Len6,
    -> SUM(LENGTH(city) = 7 ) AS Len7,
    -> SUM(LENGTH(city) = 8 ) AS Len8,
    -> SUM(LENGTH(city) = 9 ) AS Len9,
    -> SUM(LENGTH(city) = 10 ) AS Len10,
    -> SUM(LENGTH(city) = 11 ) AS Len11,
    -> SUM(LENGTH(city) = 12 ) AS Len12,
    -> SUM(LENGTH(city) > 12 ) AS Over12
    -> FROM Cities;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| Under5 | Len5   | Len6   | Len7   | Len8   | Len9   | Len10  | Len11  | Len12  | Over12 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 229941 | 197486 | 290239 | 331789 | 326917 | 288873 | 240595 | 187475 | 142805 | 463234 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
1 row in set (2.19 sec)

# EXPLAIN:
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using index |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+

mysql> SELECT
    -> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) < 5 ) AS Under5,
    -> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 5 ) AS Len5,
    -> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 6 ) AS Len6,
    -> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 7 ) AS Len7,
    -> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 8 ) AS Len8,
    -> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 9 ) AS Len9,
    -> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 10 ) AS Len10,
    -> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 11 ) AS Len11,
    -> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 12 ) AS Len12,
    -> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) > 12 ) AS Over12
    -> ;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| Under5 | Len5   | Len6   | Len7   | Len8   | Len9   | Len10  | Len11  | Len12  | Over12 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 229941 | 197486 | 290239 | 331789 | 326917 | 288873 | 240595 | 187475 | 142805 | 463234 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
1 row in set (6.44 sec)

# EXPLAIN:
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
|  1 | PRIMARY     | NULL   | NULL  | NULL          | NULL | NULL    | NULL |    NULL | No tables used           |
| 11 | SUBQUERY    | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using where; Using index |
| 10 | SUBQUERY    | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using where; Using index |
|  9 | SUBQUERY    | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using where; Using index |
|  8 | SUBQUERY    | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using where; Using index |
|  7 | SUBQUERY    | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using where; Using index |
|  6 | SUBQUERY    | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using where; Using index |
|  5 | SUBQUERY    | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using where; Using index |
|  4 | SUBQUERY    | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using where; Using index |
|  3 | SUBQUERY    | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using where; Using index |
|  2 | SUBQUERY    | Cities | index | NULL          | city | 767     | NULL | 2686372 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+

mysql> SHOW TABLE STATUS LIKE 'Cities'\G
*************************** 1. row ***************************
           Name: cities
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2686372
 Avg_row_length: 66
    Data_length: 179077120
Max_data_length: 0
   Index_length: 232341504
      Data_free: 7340032
 Auto_increment: 2699355
    Create_time: 2014-03-08 16:19:29
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 2097152000 |
+-------------------------+------------+
1 row in set (0.02 sec)

mysql>

Notes:
* There is a KEY(city). (But this is not critical to the argument.)
* All subqueries have to scan the entire index. (As opposed to being able to do some kind of 'range' scan.)
* The index is smaller than the buffer_pool. The timing difference would be _much_ worse otherwise.
* Yes, I did run each query twice -- to be sure that things were cached. The timings given reflect just CPU, no I/O.

In general, (and in my opinion), fetching a row is more costly than computing a function (or aggregate).
With subqueries, 10*2686372 rows were fetched, plus 10*2686372 LENGTHs and 1*2686372 COUNTs.
With aggregates, 1*2686372 rows were fetched, plus 10*2686372 LENGTHs and 10*2686372 SUMs.

Options: ReplyQuote


Subject
Written By
Posted
March 01, 2014 02:42PM
March 01, 2014 11:28PM
May 26, 2014 11:32PM
Re: Complex queries
May 27, 2014 02:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.