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.

Table structure:

mysql> show create table transaction_event_items\G
*************************** 1. row ***************************
Table: transaction_event_items
Create Table: CREATE TABLE `transaction_event_items` ( `tei_row_id` bigint(20) NOT NULL AUTO_INCREMENT,`tei_adp_type` 
varchar(16) NOT NULL,`tei_trn_code` varchar(64) DEFAULT NULL,`tei_trn_date` datetime NOT NULL DEFAULT '2020-01-01 01:00:00',
`tei_item_seq` int(11) DEFAULT NULL,`tei_item_cln` int(11) DEFAULT NULL,`tei_item_type` varchar(16) NOT NULL `tei_item_code`
varchar(32) NOT NULL,`tei_item_date` timestamp NOT NULL DEFAULT '2020-01-01 01:00:00',`tei_item_amount` decimal(8,2) DEFAULT NULL,
`tei_item_description` varchar(128) DEFAULT NULL,`tei_att_row_id` int(11) NOT NULL, `tei_lastupdt_date` timestamp NOT NULL DEFAULT
CURRENT_TIMESTAMP,`tei_phy_id` varchar(64) DEFAULT NULL,`tei_item_quantity` int(10) DEFAULT '1',`tei_item_employee` varchar(64)
DEFAULT NULL,KEY `tei_row_id` (`tei_row_id`),KEY `tei_phy_id_trn_date_idx` (`tei_phy_id`,`tei_trn_date`),KEY `tei_item_code_date` 
(`tei_phy_id`,`tei_item_code`,`tei_trn_date`),KEY `tei_item_description_date` (`tei_phy_id`,`tei_item_description`,`tei_trn_date`),
KEY `idx_tei_item_date` (`tei_item_date`) ) ENGINE=MyISAM AUTO_INCREMENT=2845272905 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE
(to_days(`tei_trn_date`))(PARTITION p111109 VALUES LESS THAN (734816) ENGINE = MyISAM, PARTITION p111110 VALUES LESS THAN (734817) ENGINE = MyISAM,
PARTITION p111111 VALUES LESS THAN (734818) ENGINE = MyISAM,
PARTITION p111112 VALUES LESS THAN (734819) ENGINE = MyISAM,
...
PARTITION p121129 VALUES LESS THAN (735202) ENGINE = MyISAM,
PARTITION p121130 VALUES LESS THAN (735203) ENGINE = MyISAM,
PARTITION p121201 VALUES LESS THAN (735204) ENGINE = MyISAM,
PARTITION p121202 VALUES LESS THAN (735205) ENGINE = MyISAM)

Query:

SELECT
    tei_phy_id as Physical_ID__c
  , max(tei_trn_date) as Last_Event_Received__c
  , tei_adp_type as Last_Event_Adapter_Type__c
FROM
    transaction_event_items t
WHERE
    tei_trn_date > date_sub(now(), interval 2 day)
GROUP BY
    tei_phy_id;

Explain:

+----+-------------+-------+------+---------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 10072085 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------------------------------------+

This query is not using any indexes and partitions in the table. Table size is 160gb.

What can I do to make it use indexes and partitions?

share|improve this question
    
cross-posted on SO -- stackoverflow.com/questions/13176103/… –  bluefeet Nov 1 '12 at 11:42
    
Please do not post the same question to multiple sites. Pick the site and try there, and if it is closed as off-topic, ask on the site you are told to use when it is closed. –  hexafraction Nov 1 '12 at 12:45
    
Not familiar with MySQL, but shouldn't you be using the same function you use for partitioning in the where clause? –  Mat Nov 5 '12 at 8:06

2 Answers 2

Quite often MySql does not use index, because index should be of the same data type as filter value. You have partitioning on a function to_days(tei_trn_date). Hence your query should have:

WHERE  to_days(tei_trn_date) > to_days(date_sub(now(), interval 2 day))

Does your version of MySQL support partitioning by date? Partitioning by date column would make use of ranges easier in your code.

share|improve this answer

As per my understanding, you are using a function in the where clause. That's why the explain plan is not able to identify the exact partition number in table's partitions.

Function You used : (date_sub(now(), interval 2 day))

For using index: you have to create an individual index for that column like

create index index_tei_trn_date on transaction_event_items(tei_trn_date);

If explain plan still doesn't use the index, use hint index. For more details on this read: http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

share|improve this answer

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.