Mysql: Large Query with subqueries optimization

Question

Any query optimization gurus in the house??

Anyway, I trying to attach this on two fronts, one I'm trying to code a way around it but secondly, I'm thinking some real optimization might help in the short trem 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)

Indexes:

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

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)

Transactions is about 3000 rows Tickets is about 6000 rows Sales Outlets is 3 rows Ticket_types is 18 rows

Thanks!

More information is required: sql explain, indexes, some data statistics( number of rows etc)
Q: The "Events" table... is each "ID" unique per date/time start of the given event -- just confirming...
correct, the id is autogenerated and the start_time is a timedate that is also unique for each event.

DRapp · Accepted Answer · 2012-11-08 18:29:51Z

It made my head hurt, but just a bit... It appears each event ID is a specific date/time, such as movies (which it appears) one event is the movie at 2:30 on Day X, another event is the movie @ 4:45 on Day X. The same movie on Day Y @ 2:30 would be a different ID... That said, you are trying to breakdown counts that are box-office specific vs web-based transactions. If box-office, you are tracking which specific location via the Sales Outlet location name, otherwise, just use the description of the "WebTransaction".. because anyone can buy a movie ticket and be applied to any theatre it is associated with. Again, based on speculation of your data context.

That said, I would just pre-query all tickets per specific location per Event ID... Get the count, cost basis per ticket, etc which is the "PreQuery", yet pulling along for the ride the date_start.

Now, roll-them-up with the OUTER query to just group by date of the event and where and SUMming() the total tickets, revenue and value per individual ID (event) and grouping by the given specific DAY.

Since I don't have actual data to apply, you MAY have slight adjustments to tweak...

SELECT STRAIGHT_JOIN
      DATE( PreQuery.Start_Time ),
      PreQuery.Location,
      PreQuery.Price_Type,
      PreQuery.Cost,
      SUM( PreQuery.NumberOfTicketIDs ) as NumTicketsForDay,
      SUM( PreQuery.Value ) as TicketValueForDay,
      SUM( PreQuery.Revenue ) as TicketRevenueForDay
   from
      ( select
              EV.ID as WebEventID,
              EV.Start_Time,
              TR.Type
              IF( TR.Type = 'BoxOfficeTransaction', SO.name, TR.Type ) as Location,
              TT.Name as Price_Type,
              TT.Price as Cost,
              count(TICK.id) NumberOfTicketIDs,
              SUM( TICK.Quantity * TT.Price ) as Value,
              SUM( TICK.Purchase_Price * TICK.Quantity ) as Revenue
           from 
              events EV

                 JOIN tickets TICK
                    ON EV.ID = TICK.Event_ID
                   AND TICK.Status = 'active'

                    JOIN transactions TR
                       ON TICK.Transaction_ID = TR.ID

                       LEFT JOIN ticket_agents TA
                          ON TR.Ticket_Agent_ID = TA.ID
                          LEFT JOIN Sales_Outlets SO
                             ON TA.Sales_Outlet_ID = SO.ID
                    JOIN ticket_types TT
                       ON TICK.Ticket_Type_ID = TT.ID
           where
                  EV.start_time >= '2012-11-15 05:00:00' 
              and EV.start_time <= '2013-01-06 04:59:59' 
           group by 
              EV.ID,
              TR.Type
              IF( TR.Type = 'BoxOfficeTransaction', SO.name, TR.Type ) ) as PreQuery
   group by
      DATE( PreQuery.Start_Time ),
      PreQuery.Location,
      PreQuery.Price_Type,
      PreQuery.Cost

This is amazing, I've never built a query like this before. I'm having a issue with quantity, i figured that NumberOfTicketIDs was quantity, but then I received and error you can use an alias sum function, which i know, so then I tried to put the count sum in value and revenue line and then got "Invalid use of group function." This is amazing work, I figured I ask before I destroyed any elegance.
hold the phone! I figured it out! Thank you so much. My Query 21 secs, your query 0.17 secs! Thank you thank you!
@RossR, Yeah, but please ensure the accuracy... If any duplicate keys that might cause a Cartesian result, but glad it was just a LITTLE bit faster... :)
no it worked like a charm compared the results and it was spot on. If you want to look at 8 other queries, I'd be happy to pay your hourly rate! Everyone like faster!
@RossR, thanks for the offer, but just post it here so others can benefit from problem solving solutions that they too might have similar issues with in their environments.

Marc Alff · Answer 2 · 2012-11-08 15:18:59Z

up vote 0 down vote

To have a better idea of:

how this query is executed,
how much table IO is done against which table,

you can use the technique used in this answer:

Why does MySQL's performance decrease when queries are executed in parallel?

and look at statistics collected by the performance_schema, by table or by index.

answered Nov 8 '12 at 15:18

Marc Alff
1584

	Thanks for the response. So the concept, if I understand it, is to change the key matches to not matching and run a explain to compare, correct? My follow up is should I do that for all joins, just the wheres, or all of the above? Thanks! – Ross R Nov 8 '12 at 15:43
	keys that matches or not was a question specific to that post, you can forget this part. The technique is to use select * from performance_schema.objects_summary_global_by_type to see how much table io is done, detailed by table. – Marc Alff Nov 8 '12 at 15:54
	Sorry, I missed that whole part I guess... My user doesn't have rights to the performance_schema, let me find out if I can get it. – Ross R Nov 8 '12 at 16:01

asked	6 months ago
viewed	103 times
active	6 months ago

Mysql: Large Query with subqueries optimization

migrated from stackoverflow.com Nov 11 '12 at 18:58

2 Answers

Your Answer

Mysql: Large Query with subqueries optimization

migrated from stackoverflow.com Nov 11 '12 at 18:58

2 Answers

Your Answer

Sign up or log in

Post as a guest

Related