how optimize this query?

MySQL & MariaDB Other MySQL® Questions
1

My table has 70 columns… too many I know… anyway I need to optimize this slow query:

EXPLAIN SELECT COUNT(*) FROM MY_ORDER WHERE MY_DATE>=DATE_SUB(CURDATE(), interval 15 day) AND MY_STATUS != ‘5’ AND MY_STATUS != ‘10’ AND MY_PHONE=‘14112319’ ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA1 SIMPLE MY_ORDER range MY_DATE,MY_STATUS MY_STATUS 4 \N 737002 Using where

Do you have any idea?

Thank you!

2

You could create an index that covers all of the criteria. That index could be:

create index new_index(MY_STATUS, MY_PHONE, MY_DATE);

That order might work well, although you could play around with different orders for the multi-column index. Trial and error!