0

We have a SQL query that is doing a full table scan:

DELETE 
  FROM oc_cart 
 WHERE (api_id > ? OR customer_id = ?) 
   AND date_added < DATE_SUB(NOW(), INTERVAL ? HOUR)

Is it possible to create an index to optimize this query?

For now I have create 2 indices:

  • one on date_added
  • one on (date_added, customer_id and api_id)

But it seems that the query is still doing full scan.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
redochka
  • 12,345
  • 14
  • 66
  • 79
  • The index on date_added is redundant. (I think I'm right in sayng that) The other index is only partially usable. If (date_added, customer_id) doesn't help, try (date_added,api_id), and try swapping around the order of indexes. You could take a more scientific approach, but the permutations here are so few that it'll be quicker just to 'suck it and see' – Strawberry Nov 19 '20 at 16:58

1 Answers1

0

How many rows in the table? What does SHOW CREATE TABLE provide? EXPLAIN SELECT ...`?

It is not possible to optimize the query much.

If api_id > ? is more selective than date added < ..., then this may be the best:

DELETE 
  FROM oc_cart 
 WHERE api_id > ?
   AND date_added < DATE_SUB(NOW(), INTERVAL ? HOUR);
DELETE 
  FROM oc_cart 
 WHERE customer_id = ?
   AND date_added < DATE_SUB(NOW(), INTERVAL ? HOUR);

together with these two indexes:

INDEX(api_id, date_added)
INDEX(customer_id, date_added)

In case api_is not that selective, also add

INDEX(date_added, api_id)  (but _not_ INDEX(date_added))
Rick James
  • 135,179
  • 13
  • 127
  • 222