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.

Any query optimization gurus in the house??

Anyway, I am trying to attack this on two fronts, one being to code a way around it but secondly, I'm thinking some real optimization might help in the short term while we create a more complex, more robust fix. So without further exposition, here's the query:

select 
    Date(events.start_time) as event_date, 
    if(transactions.type = 'BoxOfficeTransaction', (
            select sales_outlets.name 
            from ticket_agents, sales_outlets 
            where 
                transactions.ticket_agent_id = ticket_agents.id 
                and ticket_agents.sales_outlet_id = sales_outlets.id
        ), transactions.type
    ) as location, 
    ticket_types.name as price_type, 
    ticket_types.price as cost, 
    if (transactions.type = 'WebTransaction', (
            select count(tickets.id) 
            from tickets, transactions, events, ticket_types 
            where 
                tickets.transaction_id = transactions.id 
                and tickets.event_id = events.id 
                and tickets.ticket_type_id = ticket_types.id 
                and transactions.type = 'WebTransaction' 
                and ticket_types.name = price_type 
                and Date(events.start_time) = event_date 
                and tickets.status = 'active'
        ), (
            select count(tickets.id) 
            from tickets, transactions, events, ticket_types, ticket_agents, sales_outlets 
            where 
                tickets.transaction_id = transactions.id 
                and tickets.event_id = events.id 
                and tickets.ticket_type_id = ticket_types.id 
                and transactions.ticket_agent_id = ticket_agents.id 
                and ticket_agents.sales_outlet_id = sales_outlets.id 
                and ticket_types.name = price_type 
                and Date(events.start_time) = event_date 
                and tickets.status = 'active' 
                and sales_outlets.name = location
        ) 
    ) as quantity, 
    (select quantity * ticket_types.price) as value, 
    (select tickets.purchase_price * quantity) as revenue 
from events, transactions, tickets, ticket_types 
where 
    transactions.event_id = events.id 
    and tickets.transaction_id = transactions.id 
    and tickets.ticket_type_id = ticket_types.id 
    and events.start_time >= '2012-11-15 05:00:00' 
    and events.start_time <= '2013-01-06 04:59:59' 
group by event_date, location, ticket_types.name

A doozy I know. Here's the explain:

+----+--------------------+---------------+--------+-------------------------------------+-------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
| id | select_type        | table         | type   | possible_keys                       | key               | key_len | ref                                   | rows | Extra                                                     |
+----+--------------------+---------------+--------+-------------------------------------+-------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
|  1 | PRIMARY            | events        | index  | PRIMARY,event_date                  | event_date        | 13      | NULL                                  |   53 | Using where; Using index; Using temporary; Using filesort |
|  1 | PRIMARY            | transactions  | ref    | PRIMARY,event_id                    | event_id          | 5       | abg_dev.events.id                     |   23 | Using where                                               |
|  1 | PRIMARY            | tickets       | ref    | trans_id,ticket_types_id            | trans_id          | 5       | abg_dev.transactions.id               |    2 | Using where                                               |
|  1 | PRIMARY            | ticket_types  | eq_ref | PRIMARY                             | PRIMARY           | 4       | abg_dev.tickets.ticket_type_id        |    1 |                                                           |
|  4 | DEPENDENT SUBQUERY | sales_outlets | ref    | PRIMARY,sales_outlet_name           | sales_outlet_name | 767     | func                                  |    1 | Using where; Using index                                  |
|  4 | DEPENDENT SUBQUERY | ticket_types  | ref    | PRIMARY,type_name,ticket_types_name | type_name         | 767     | func                                  |    4 | Using index                                               |
|  4 | DEPENDENT SUBQUERY | tickets       | ref    | trans_id,ticket_types_id            | ticket_types_id   | 5       | abg_dev.ticket_types.id               |  441 | Using where                                               |
|  4 | DEPENDENT SUBQUERY | events        | eq_ref | PRIMARY                             | PRIMARY           | 4       | abg_dev.tickets.event_id              |    1 | Using where                                               |
|  4 | DEPENDENT SUBQUERY | transactions  | eq_ref | PRIMARY,ticket_agent_id             | PRIMARY           | 4       | abg_dev.tickets.transaction_id        |    1 |                                                           |
|  4 | DEPENDENT SUBQUERY | ticket_agents | eq_ref | PRIMARY                             | PRIMARY           | 4       | abg_dev.transactions.ticket_agent_id  |    1 | Using where                                               |
|  3 | DEPENDENT SUBQUERY | ticket_types  | ref    | PRIMARY,type_name,ticket_types_name | type_name         | 767     | func                                  |    4 | Using index                                               |
|  3 | DEPENDENT SUBQUERY | tickets       | ref    | trans_id,ticket_types_id            | ticket_types_id   | 5       | abg_dev.ticket_types.id               |  441 | Using where                                               |
|  3 | DEPENDENT SUBQUERY | transactions  | eq_ref | PRIMARY                             | PRIMARY           | 4       | abg_dev.tickets.transaction_id        |    1 | Using where                                               |
|  3 | DEPENDENT SUBQUERY | events        | eq_ref | PRIMARY                             | PRIMARY           | 4       | abg_dev.tickets.event_id              |    1 | Using where                                               |
|  2 | DEPENDENT SUBQUERY | ticket_agents | eq_ref | PRIMARY                             | PRIMARY           | 4       | transactions.ticket_agent_id          |    1 |                                                           |
|  2 | DEPENDENT SUBQUERY | sales_outlets | eq_ref | PRIMARY                             | PRIMARY           | 4       | abg_dev.ticket_agents.sales_outlet_id |    1 |                                                           |
+----+--------------------+---------------+--------+-------------------------------------  +-------------------+---------+---------------------------------------+------+-----------------------------------------------------------+
16 rows in set, 2 warnings (0.00 sec) 

Index Info:

mysql> show indexes from transactions;
+--------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| transactions |          0 | PRIMARY             |            1 | id                  | A         |        1274 |     NULL | NULL   |      | BTREE      |         |               |
| transactions |          1 | ticket_agent_id     |            1 | ticket_agent_id     | A         |          41 |     NULL | NULL   | YES  | BTREE      |         |               |
| transactions |          1 | ticket_agent_id     |            2 | id                  | A         |        1274 |     NULL | NULL   |      | BTREE      |         |               |
| transactions |          1 | user_id             |            1 | user_id             | A         |        1274 |     NULL | NULL   |      | BTREE      |         |               |
| transactions |          1 | user_id             |            2 | id                  | A         |        1274 |     NULL | NULL   |      | BTREE      |         |               |
| transactions |          1 | payment_method_type |            1 | payment_method_type | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| transactions |          1 | payment_method_type |            2 | id                  | A         |        1274 |     NULL | NULL   |      | BTREE      |         |               |
| transactions |          1 | created_at          |            1 | created_at          | A         |        1274 |     NULL | NULL   |      | BTREE      |         |               |
| transactions |          1 | created_at          |            2 | id                  | A         |        1274 |     NULL | NULL   |      | BTREE      |         |               |
| transactions |          1 | created_at_by_user  |            1 | created_at          | A         |        1274 |     NULL | NULL   |      | BTREE      |         |               |
| transactions |          1 | created_at_by_user  |            2 | user_id             | A         |        1274 |     NULL | NULL   |      | BTREE      |         |               |
| transactions |          1 | event_id            |            1 | event_id            | A         |          55 |     NULL | NULL   | YES  | BTREE      |         |               |
| transactions |          1 | event_id            |            2 | id                  | A         |        1274 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
13 rows in set (0.00 sec)

mysql> show indexes from tickets;
+---------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tickets |          0 | PRIMARY         |            1 | id             | A         |        4330 |     NULL | NULL   |      | BTREE      |         |               |
| tickets |          0 | barcode_index   |            1 | barcode        | A         |        4330 |     NULL | NULL   | YES  | BTREE      |         |               |
| tickets |          1 | trans_id        |            1 | transaction_id | A         |        2165 |     NULL | NULL   | YES  | BTREE      |         |               |
| tickets |          1 | trans_id        |            2 | id             | A         |        4330 |     NULL | NULL   |      | BTREE      |         |               |
| tickets |          1 | ticket_types_id |            1 | ticket_type_id | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |               |
| tickets |          1 | ticket_types_id |            2 | id             | A         |        4330 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

mysql> show indexes from sales_outlets;
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sales_outlets |          0 | PRIMARY           |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| sales_outlets |          1 | sales_outlet_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| sales_outlets |          1 | sales_outlet_name |            2 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> show indexes from ticket_types;
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ticket_types |          0 | PRIMARY           |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| ticket_types |          1 | type_name         |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| ticket_types |          1 | type_name         |            2 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| ticket_types |          1 | price             |            1 | price       | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| ticket_types |          1 | price             |            2 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| ticket_types |          1 | ticket_types_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| ticket_types |          1 | ticket_types_name |            2 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)
share|improve this question
    
Seems like you should be able to LEFT OUTER JOIN to those tables, rather than subselecting them. –  Alain Collins Nov 15 '12 at 7:05
    
Are there indexes on the 'events' table? Can you show those? –  Valerie Parham-Thompson May 14 '13 at 0:47
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.