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.

After browsing most of the posts and after running out for options I am posting here. I have a table with 76,222,954 (76M) rows. It collects web site traffic based on country.

Here is my table structure:

CREATE TABLE `publication_month` (
  `entity_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `region_id` int(4) NOT NULL,
  `partner_id` int(11) NOT NULL DEFAULT '-1',
  `country_code` int(3) NOT NULL DEFAULT '0',
  `metric` int(2) NOT NULL,
  `time_index` int(11) NOT NULL DEFAULT '0',
  `mobile_value` bigint(20) DEFAULT '0',
  `desktop_value` bigint(20) DEFAULT '0',
  `mobile_last_value` bigint(20) DEFAULT '0',
  `desktop_last_value` bigint(20) DEFAULT '0',
  `flag` int(1) NOT NULL,
  `area_id` int(11) NOT NULL,
  PRIMARY KEY (`entity_id`,`category_id`,`region_id`,`partner_id`,`country_code`,
               `metric`,`time_index`,`area_id`),
  KEY `group_by_index` (`time_index`,`metric`,`partner_id`),
  KEY `distinct_entities` (`region_id`,`category_id`,`time_index`,`entity_id`,`partner_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( time_index)
(PARTITION 2008_09 VALUES LESS THAN (2008100000) ENGINE = MyISAM,
 PARTITION 2008_10 VALUES LESS THAN (2008110000) ENGINE = MyISAM,
// many partitons
 PARTITION 2012_11 VALUES LESS THAN (2012120000) ENGINE = MyISAM,
 PARTITION 2012_12 VALUES LESS THAN (2013010000) ENGINE = MyISAM,
 PARTITION catchall VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ 

Here are my index information

mysql> show keys from publication_month;
+-------------------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table             | Non_unique | Key_name          | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| publication_month |          0 | PRIMARY           |            1 | entity_id    | A         |      115140 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          0 | PRIMARY           |            2 | category_id  | A         |      266513 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          0 | PRIMARY           |            3 | region_id    | A         |     1249556 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          0 | PRIMARY           |            4 | partner_id   | A         |     1732339 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          0 | PRIMARY           |            5 | country_code | A         |    15244590 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          0 | PRIMARY           |            6 | metric       | A         |    76222954 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          0 | PRIMARY           |            7 | time_index   | A         |    76222954 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          0 | PRIMARY           |            8 | area_id      | A         |    76222954 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          1 | group_by_index    |            1 | time_index   | A         |          13 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          1 | group_by_index    |            2 | metric       | A         |          68 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          1 | group_by_index    |            3 | partner_id   | A         |         340 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          1 | distinct_entities |            1 | region_id    | A         |          95 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          1 | distinct_entities |            2 | category_id  | A         |        4493 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          1 | distinct_entities |            3 | time_index   | A         |        4493 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          1 | distinct_entities |            4 | entity_id    | A         |     1249556 |     NULL | NULL   |      | BTREE      |         |
| publication_month |          1 | distinct_entities |            5 | partner_id   | A         |     1732339 |     NULL | NULL   |      | BTREE      |         |
+-------------------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

Here is the query most of the times I run.

SELECT traffic.entity_id, traffic.metric, traffic.partner_id
FROM publication_month AS traffic
WHERE traffic.region_Id IN (0,1,2,3,4,5,6) AND traffic.category_id =533 AND
      traffic.time_index BETWEEN 2012050100 AND 2012080100 AND 
      traffic.partner_id IN (-999,-1) AND  traffic.entity_id > 0 AND  
      traffic.country_code  >=0 AND traffic.metric IN (1,2,3,4,5) 
GROUP BY traffic.entity_id, traffic.metric, traffic.partner_id;

More or less all my queries will as above or some times I may have to query upon a group of countries or group of entity_ids.

Here is the EXPLAIN output for the above query

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: traffic
         type: range
possible_keys: PRIMARY,group_by_index,distinct_entities
          key: distinct_entities
      key_len: 16
          ref: NULL
         rows: 485802
        Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

It is scanning for 485,802 rows to return 1500 rows. I have tried to create an index on country-time-entitid-category-id but that couldn't help me.

Any ideas or suggestions I would love to hear it.

share|improve this question
    
Please provide more info: how long does the query run. How many days of traffic info does the table contain (maybe you can partition by day). What do you want to accomplish ? –  MTIhai Sep 28 '12 at 10:38
    
I have five years worth of data. Currently depending on the number of countries or for a world wide view the query executes some where between 40 seconds to 80 seconds. My accomplishment is certainly to reduce the query execution time. –  Jai Prakash Oct 2 '12 at 1:36

1 Answer 1

region_id int(4) NOT NULL,

Learn about TINYINT UNSIGNED (1 byte) and SMALLINT UNSIGNED (2 bytes). INT is always 4 bytes. Smaller -> more cacheable -> faster.

WHERE traffic.region_Id IN (0,1,2,3,4,5,6) AND traffic.category_id =533 AND
  traffic.time_index BETWEEN 2012050100 AND 2012080100 AND 
  traffic.partner_id IN (-999,-1) AND  traffic.entity_id > 0 AND  
  traffic.country_code  >=0 AND traffic.metric IN (1,2,3,4,5) 

For an index to be useful, it should start with "=" fields and "IN" fields. After that, only one "range" field will be used. So:

INDEX(category_id, region_Id, Partner_id, xx) -- the order of the first 3 is not critical; start xx with whatever is usually most selective (other than time_index - see below).

Generally, when PARTITIONing, it is best to put the partition key (time_index) last in any indexes it is in. The range key is used for "partition pruning" before the INDEX is used. So, even if is too late in the index, that's ok.

Use EXPLAIN PARTITIONS SELECT ... to see that it is doing the "pruning" correctly.

share|improve this answer
    
Thanks Rick for your comments. The table under question is gets regular inserts and frequent reads. So i was investigating if i can optimize the query in any way and decrease the query response time. I created new indexes as per your suggestion except for the world wide data (country_code >= 0) all the queries are running at much better now. I have one more doubt, is there any performance impact comparing signed and unsigned integers. –  Jai Prakash Oct 2 '12 at 1:33
    
Performance diff -- none. Do not mix signed and unsigned. Best practice: Use UNSIGNED where it makes sense. –  Rick James Oct 3 '12 at 0:13

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.