I have the table: orders with the following columns

(orderNumber, orderDate, shippedDate, status, users_userId)

I indexed orderDate, shippedDate, status for performing similar queries to the following:

SELECT orderNumber FROM orders WHERE status='shipped' 
ORDER BY orderDate, shippedDate;

Since there are only about 14 possible statutes for an order which I thought would speed up the search whenever I need to select range of rows of orders with a specific status.

Also since indexes are sorted,creating an index on the orderDate and shippedDate it allows a query's ORDER BY specification to be met without a separate sorting step.

I just want to know if this sounds correct and if am I really optimizing my query in this way?

Here is the EXPALIN output of my query:

id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: composite_index
key: composite_index
key_len: 138
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Using index
  • 1
    It pretty much depends on the actual data and benchmarking is the only way to know for sure. You can check the query plan for your current data and queries with EXPLAIN Dec 29 '17 at 21:39
  • Thank´s for the hint. I have just added above the explain output of my query.
    – codeDragon
    Dec 30 '17 at 10:40

For this query:

SELECT `orderNumber`
FROM orders
WHERE `status` = 'shipped’ 
ORDER BY orderDate, shippedDate;

Try a composite index on orders(status, orderDate, shippedDate, orderNumber).

  • I would not index orderNumber since it's UNIQE.
    – codeDragon
    Dec 30 '17 at 10:00
  • It is OK to have `orderNumber in this index. The way Gordon wrote it is optimal, "covering", etc. More on creating good indexes: mysql.rjweb.org/doc.php/index_cookbook_mysql
    – Rick James
    Dec 31 '17 at 19:51

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.