1

I have a large indexed table with over 35 million rows. Here are the indexes, with a naming convention that shows their sequential column order:

IDX_E93438363DAE128B,
IDX_LIST_ID_EMAIL_ADDRESS_DELETED,
IDX_LIST_ID_SUBSCRIBED_DELETED_EMAIL_ADDRESS,
IDX_LIST_ID_SUBSCRIBED_DELETED_FIRST_NAME,
IDX_LIST_ID_SUBSCRIBED_DELETED_LAST_UPDATED_AT

My query of this table on my medium Amazon RDS instance is taking upwards of 15 minutes:

SELECT * FROM contact
WHERE list_id = '014c7cbe-c124-11e5-b4ea-0a4287b2e8c5'
AND subscribed = 1
AND deleted = 0

Using an EXPLAIN of the query I can see that MySQL is choosing to use the IDX_LIST_ID_EMAIL_ADDRESS_DELETED key, when I'd expect most of the other keys (IDX_LIST_ID_SUBSCRIBED_DELETED_...) to be more optimal.

Should I just trust the engine? Is 17 or so minutes a realistic time to wait for a query on a table this large?

4
  • 2
    Please provide SHOW CREATE TABLE; there could be a datatype problem.
    – Rick James
    Jan 23 '16 at 18:35
  • 1
    Also could you please update the question with the output of show indexes from contact? Cardinality plays a big role too. Jan 25 '16 at 13:11
  • I discovered something peculiar with MySQL optimizer, see: dba.stackexchange.com/questions/178313/…. What happens if you create your indexes in a different order?
    – Lennart
    Jul 10 '17 at 14:22
  • How many rows have list_id = '014c7cba-c124-11e5-b4ea-0a4287b2e8c5'?
    – Rick James
    Jul 10 '17 at 17:25
0

17 minutes is not a reasonable time to wait. Try to provide an index hint:

SELECT *
FROM contact USE INDEX (IDX_LIST_ID_SUBSCRIBED_DELETED_EMAIL_ADDRESS)
WHERE list_id = '014c7cba-c124-11e5-b4ea-0a4287b2e8c5'
AND subscribed = 1
AND deleted = 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.