How to search with partitions in my query? Table structure:

mysql&gt; 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 &gt; 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?
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