Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to understand whether I'm doing something wrong, or expecting too much.

I have an InnoDB table on a db.m1.large RDS (MySQL 5.6.13. 7.5 GB RAM) with ~1M rows.

The primary key is a composite one. Having 1 TIMESTAMP and 3 BIGINTs.

pk1 is the TIMESTAMP and pk2 is a BIGINT.

I don't have any other indices, only the primary key.

Both of these queries run for ~1.2 seconds.

select e3.pk2 from table e3 where e3.pk1 BETWEEN '2013-09-24 12:27:00.0' AND '2013-11-29 11:27:00.0' group by e3.pk2 order by null;

select distinct e3.pk2 from table e3 where e3.pk1 BETWEEN '2013-09-24 12:27:00.0' AND '2013-11-29 11:27:00.0' order by null;

Running describe gives me

 | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                     |
 |  1 | SIMPLE      | e3    | range | PRIMARY       | PRIMARY | 4       | NULL | 493481 | Using where; Using index; Using temporary |

innodb_buffer_pool_size is 5.7GB. innodb_log_buffer_size is 8M. max_heap_table_size is 256MB.

Is the performance ok? for selecting distinct over ~500k records? Should I be considering something else?

EDIT

create statement:

CREATE TABLE `table` (
  `pk1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `pk2` int(11) NOT NULL,
  `pk3` bigint(20) NOT NULL DEFAULT '0',
  `pk4` bigint(20) NOT NULL DEFAULT '0',
  `col1` int(11) DEFAULT NULL,
  `col2` bigint(20) DEFAULT '0',
  `col3` bigint(20) DEFAULT '0',
  `col4` bigint(20) DEFAULT '0',
  PRIMARY KEY (`pk1`,`pk2`,`pk3`,`pk4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Profiling:

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000123 |
| checking permissions | 0.000023 |
| Opening tables       | 0.000043 |
| init                 | 0.000052 |
| System lock          | 0.000025 |
| optimizing           | 0.000025 |
| statistics           | 0.000139 |
| preparing            | 0.000036 |
| Creating tmp table   | 0.000059 |
| executing            | 0.000016 |
| Sending data         | 1.225919 |
| end                  | 0.000047 |
| removing tmp table   | 0.000067 |
| end                  | 0.000025 |
| query end            | 0.000024 |
| closing tables       | 0.000033 |
| freeing items        | 0.000085 |
| cleaning up          | 0.000018 |
+----------------------+----------+
18 rows in set, 1 warning (0.01 sec)

EDIT 2

+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+---------------------------+------------------+-------------+
| Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function           | Source_file      | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+---------------------------+------------------+-------------+
| starting             | 0.000123 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                      | NULL             |        NULL |
| checking permissions | 0.000023 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access              | sql_parse.cc     |        5328 |
| Opening tables       | 0.000043 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables               | sql_base.cc      |        4911 |
| init                 | 0.000052 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_prepare_select      | sql_select.cc    |        1050 |
| System lock          | 0.000025 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables         | lock.cc          |         304 |
| optimizing           | 0.000025 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize                  | sql_optimizer.cc |         138 |
| statistics           | 0.000139 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize                  | sql_optimizer.cc |         381 |
| preparing            | 0.000036 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize                  | sql_optimizer.cc |         500 |
| Creating tmp table   | 0.000059 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | create_intermediate_table | sql_executor.cc  |         207 |
| executing            | 0.000016 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                      | sql_executor.cc  |         110 |
| Sending data         | 1.225919 | 1.276080 |   0.000000 |                36 |                  20 |            0 |            16 |             0 |                 0 |                 0 |                 3 |     0 | exec                      | sql_executor.cc  |         187 |
| end                  | 0.000047 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_select      | sql_select.cc    |        1105 |
| removing tmp table   | 0.000067 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | free_tmp_table            | sql_tmp_table.cc |        1852 |
| end                  | 0.000025 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | free_tmp_table            | sql_tmp_table.cc |        1881 |
| query end            | 0.000024 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command     | sql_parse.cc     |        5038 |
| closing tables       | 0.000033 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command     | sql_parse.cc     |        5086 |
| freeing items        | 0.000085 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse               | sql_parse.cc     |        6374 |
| cleaning up          | 0.000018 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command          | sql_parse.cc     |        1764 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+---------------------------+------------------+-------------+

EDIT 3

As requested the total number of distinct pk2 is: 318

share|improve this question
    
So we still and "show create table " statement output.. and please also run an profile on that query... SET profiling = 1; YOUR_QUERY; SHOW PROFILES; SHOW PROFILE ALL FOR QUERY query_id (id is with in SHOW PROFILES) and also post that output here –  Raymond Nijland Nov 5 '13 at 20:25
    
based on the explain output... InnoDB engine gives an estimate off 493481 rows and need to use an WHERE condition to filter out more records (so your query should consume some CPU cycles) .. type range tells me InnoDB chooses to do an Disk scan based on PRIMARY KEY range and needs to use an temporary table.. –  Raymond Nijland Nov 5 '13 at 20:29
    
added both create table and profiling to question –  Chen Harel Nov 5 '13 at 20:56
    
Can you post an SHOW PROFILE ALL FOR QUERY query_id (id is with in SHOW PROFILES) (because i want to know the CPU timings) and also the MySQL version? –  Raymond Nijland Nov 5 '13 at 21:15
    
done. it's 5.6.13 –  Chen Harel Nov 5 '13 at 21:27

1 Answer 1

up vote 2 down vote accepted

Since there are few different values for pk2 column, it might be more efficient to have an index with the reverse order (pk2, pk1):

ALTER TABLE `table`
  ADD INDEX tIX 
    (pk2, pk1) ; 

and then use the query:

SELECT e3.pk2 
FROM `table` e3 
GROUP BY e3.pk2 
HAVING EXISTS
       ( SELECT 1
         FROM `table` t
         WHERE t.pk2 = e3.pk2
           AND t.pk1 BETWEEN '2013-09-24 12:27:00.0'
                         AND '2013-11-29 12:27:00.0'
       )
-- ORDER BY NULL ;

Depending on the size of the tables and the values of the datetime range, you may get a plan that uses the primary index in the dependent subquery:

ID SELECT_TYPE     TABLE TYPE  POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1  PRIMARY            e3 range (null)        tIX    4  (null) 61  Using index for group-by
2  DEPENDENT SUBQUERY t  range PRIMARY,tIX  PRIMARY 4  (null)  4  Using where; Using index

But with a larger range or larger table (we can't test too many rows at SQLFiddle), see the SQL-Fiddle, and another possible execution plan. This one I expect to be more efficient when you have few distinct values in the pk2 column. With 318 distinct values, it will do 318 lookups using the newly added index which will be more efficient than the previous plan that first gets (possibly millions) rows that match the datetime range condition and then finds the DISTINCT pk2 values among them:

ID SELECT_TYPE     TABLE TYPE  POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1  PRIMARY            e3 range (null)        tIX    4  (null) 61  Using index for group-by
2  DEPENDENT SUBQUERY t  range PRIMARY,tIX   tIX    4   func  52  Using where; Using index
share|improve this answer
    
Thanks, this approach really speeds up things. Can you please explain the edit? why the current version is better than the one in sqlfiddle.com/#!9/faaaa/1 –  Chen Harel Nov 6 '13 at 7:16
    
The query on that one (the first edit of my post) was not equivalent to yours. It might return different result (more rows than your query.) –  ypercube Nov 6 '13 at 7:22
    
The three queries produce the same results (I've tried multiple date ranges) - can you please explain a bit further so I can understand the difference better? –  Chen Harel Nov 6 '13 at 7:33
    
Try with a very short range. The query with HAVING MAX(e3.pk1) >= '2013-01-01' AND MIN(e3.pk1) <= '2013-01-02' may produce more rows than the others. –  ypercube Nov 6 '13 at 7:39
    
Cool. Using a short range query confirmed your concerns. For some reason it appears that the query in your answer gets slower as the range gets smaller, does that make sense? –  Chen Harel Nov 6 '13 at 8:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.