Improve Multiple Join Query

MySQL & MariaDB Other MySQL® Questions
1

I have a orders database setup with the following

Customers
Orders (which references Customers)
LineItems (which references Orders)

I’m trying to write a query that will return the top ten states based on number of line items.

My query is:
SELECT count() AS count_all, c.state AS c_state FROM line_items as li inner join orders as o on li.order_id = o.id inner join customers as c on o.customer_id = c.id GROUP BY c.state ORDER BY count() DESC;

The problem is that mysql does not use an index for one of the queries made. Here is the explain:

±—±------------±------±-----±------------------------------±-----------------------±--------±--------------±-----±--------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------±-----±------------------------------±-----------------------±--------±--------------±-----±--------------------------------+| 1 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using temporary; Using filesort | | 1 | SIMPLE | o | ref | PRIMARY,fk_orders_customer_id | fk_orders_customer_id | 4 | my_store.c.id | 1 | Using index | | 1 | SIMPLE | li | ref | fk_line_items_order_id | fk_line_items_order_id | 4 | my_store.o.id | 1 | Using index | ±—±------------±------±-----±------------------------------±-----------------------±--------±--------------±-----±--------------------------------+

As you can see in the first row mysql does not ever use the index on state in the customers table. Does anyone know why or how I can get it to do so? Any help would be greatly appreciated. Thanks!

2

You need to count number of entries for each of the states, which means every order needs to be accessed. So FullTable scan is expected for this query and you can’t get much better (well may be have index scan instead)

If you need such reporting in real-time you need counter table.

3
[B]Peter wrote on Fri, 02 February 2007 11:12[/B]
You need to count number of entries for each of the states, which means every order needs to be accessed. So FullTable scan is expected for this query and you can't get much better (well may be have index scan instead)

If you need such reporting in real-time you need counter table.

Thanks for the response Peter. I’m still confused by the EXPLAIN output from MySQL. It makes sense that it would have to go through every line item, but I don’t understand why it wouldn’t use the index for state on the customers table. Is it just not possible to use an index on a column being used for GROUP BY?