How to search with partitions in my query?

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

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

StackExchange Database Administrators Q#28009, answer score: 2