Should the value of a select max be indexed when the where clause is already indexed

Posted on

Question :

I have a customer_orders table in a schema designed to be shared between multiple companies. Each customer_order has an invoice number that should always start at 1 and increment based on company (every company has their own invoice counting).

When I create a new customer_order, I have this query to get the largest invoice number for that company and increment it.

select max(invoice) from customer_orders where customer_id = :customer_id

because customer_id already has an index is there any point in adding an index to invoice?

enter image description here

this database is on MySQL in production and sqlite3 for testing & development (ruby on rails).

Answer :

An index on (invoice) is going to typically be fairly meaningless in your case since there are many invoices with the same numbers and an invoice number by itself is almost completely meaningless in your design without knowing which customer it belongs to … so you should have a UNIQUE index on (customer_id,invoice).

ALTER TABLE customer_orders ADD UNIQUE KEY (customer_id,invoice); -- MySQL syntax

This declaration creates both an index and a constraint — it allows the server to quickly go to the appropriate customer_id and then quickly go to the largest value associated with that customer ID, all using the new index… and it also constrains you from inadvertently duplicating an invoice number for any reason — the database will refuse to allow a given customer_id to have two rows with the same value for ‘invoice’ but will still allow the ‘invoice’ value to be reused on different customers, which sounds like what you need.

If production is on MySQL, I would recommend development and testing on MySQL as well. Supporting more DBMSs than you need to is likely to be more trouble than it’s worth and you could encounter surprises in production if there are cases where SQLite and MySQL behave differently. They are certainly unlikely to optimize queries uniformly, and if you haven’t tested with the same underlying database as is used in production, then I would suggest that you really haven’t “tested.”

Leave a Reply

Your email address will not be published. Required fields are marked *