Announcement

Announcement Module
Collapse
No announcement yet.

Improve Multiple Join Query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Improve Multiple Join Query

    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.

    Comment


    • #3
      Peter wrote on Fri, 02 February 2007 11:12

      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?

      Comment


      • #4
        Because in this case it would need to access rows in random order - going by "state" index, which is slower than doing full table scan which accesses rows in mostly sequential order.

        Comment

        Working...
        X