Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

<digression> 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." </digression>

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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