in MySQL
Optimizing MIN and MAX MySQL Functions
MySQL can optimize aggregate functions like MIN and MAX as long as the columns specified are indexed. This means that, in the case of MIN and MAX, the optimizer should be able to identify the highest and lowest values of an indexed column from the B-Tree index. Say I have a table like below:
|
1 2 3 4 5 6 7 |
CREATE TABLE `history` ( `h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `u_id` int(10) unsigned NOT NULL, `cn_id` int(10) unsigned NOT NULL, `f_id` int(10) unsigned NOT NULL PRIMARY KEY (`h_id`) ) ENGINE=InnoDB |
If I want to get the MAX value for cn_id, I’d to a query like this which will be a full table scan:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql (test) > EXPLAIN SELECT MAX(cn_id) FROM history \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: history type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 19908716 Extra: 1 row in set (0.00 sec) |
If I add an index on cn_id, the query is optimized internally:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql (test) > EXPLAIN SELECT MAX(cn_id) FROM history \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away 1 row in set (0.00 sec) |
What if I’d want to get MAX(cn_id) for a particular u_id? Well, another full table scan!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql (test) > EXPLAIN SELECT MAX(cn_id) FROM history WHERE u_id = 106476 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: history type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 19908716 Extra: Using where 1 row in set (0.00 sec) |
Again the solution is simple, make sure that cn_id is part of an index including the u_id, in this case I created a composite key on (u_id, cn_id) so that once the optimizer found index records for u_id = 106476, within the same index record are the cn_id values.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql (test) > EXPLAIN SELECT MAX(cn_id) FROM history WHERE u_id = 106476 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away 1 row in set (0.00 sec) |
Till next time!
UPDATE: Based on Daniel’s comment, to demonstrate the internal optimization for MIN and MAX with the proper keys in place, here is a sample query executed:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
mysql (test) > EXPLAIN PARTITIONS SELECT MAX(h_date) FROM history WHERE t_id = 10947385 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away 1 row in set (0.00 sec) mysql (test) > pager md5sum PAGER set to 'md5sum' mysql (test) > flush status; Query OK, 0 rows affected (0.02 sec) mysql (test) > SELECT MAX(h_date) FROM history WHERE t_id = 10947385 \G 54f1cd94f9bd1390899b13244443968f - 1 row in set (0.00 sec) mysql (test) > nopager PAGER set to stdout mysql (test) > SHOW STATUS LIKE 'handler%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 16 rows in set (0.04 sec) |
