As part of a daily cron job, I need to run a query that processes a whole lot of data. This data is related to the visitors coming to a website, and updating the data with what we have captured previously.

The query relies on 2 derived tables (select queries in the FROM section), to do its work —

  new_visits.visitor_id, new_visits.visit_id, new_visits.visit_first_action_time,
  new_visits.purchased as purchased,  
  ifnull(existing_visitors.purchased, 0) as existing_purchased 

    ( SELECT          
        tv.visitor_id, tv.visit_id, tv.visit_first_action_time, 
        if(tc.idgoal=0,1,0) as purchased                       
        tbl_visit tv left outer join tbl_conversion tc         
        tv.visit_id = tc.visit_id AND tc.idgoal = 0                       
        tv.idsite= 12 AND tv.visit_id >= 477256              
      ORDER BY tv.visit_id       
      LIMIT 1000 ) new_visits

   LEFT JOIN          

   ( SELECT 
       visitor_id, max(visit_seq) as visit_seq, purchased 
       tbl_last_input_visit where site_id = 12 
     GROUP BY visitor_id ) existing_visitors      

   ON new_visits.visitor_id = existing_visitors.visitor_id 

ORDER BY new_visits.visitor_id, new_visits.visit_id;

With smaller datasets, this query works just fine. However, as the data increases, the slowly becomes progressively slower. Until a point where it starts to take around 30 seconds to executed (at the start it takes around 1.5 seconds).

The query plan is as follows —

| id | select_type | table                  | type  | possible_keys                                                                     | key           | key_len | ref               | rows    | Extra                           |
|  1 | PRIMARY     | <derived2>             | ALL   | NULL                                                                              | NULL          | NULL    | NULL              |    1000 | Using temporary; Using filesort |
|  1 | PRIMARY     | <derived3>             | ALL   | NULL                                                                              | NULL          | NULL    | NULL              |  705325 |                                 |
|  3 | DERIVED     | tbl_input_visit        | ref   | visitorid_seq,visitorid_idx                                                       | idvisitor_seq | 4       |                   |  490047 | Using where                     |
|  2 | DERIVED     | tv                     | range | PRIMARY,index_idsite_config_datetime,index_idsite_datetime,index_idsite_idvisitor | PRIMARY       | 4       | NULL              | 4781309 | Using where                     |
|  2 | DERIVED     | tc                     | ref   | PRIMARY                                                                           | PRIMARY       | 8       | tv.idvisit        |       1 | Using index                     |

At this point, one option I have explored is creation of temporary tables. However, the overhead of doing so is quite significant. I also realise that since this query relies on derived tables, MySQL will not be able to reuse any underlying indexes.

Here are the create statements for the tables involved —

CREATE TABLE `tbl_last_input_visit` (
  `site_id` int(10) unsigned NOT NULL,
  `visitor_id` binary(8) NOT NULL,
  `visit_seq` int(10) unsigned NOT NULL,
  `purchase_cycle_seq` int(10) unsigned NOT NULL,
  `visit_in_cycle_seq` int(10) unsigned NOT NULL,
  `purchased` smallint(5) unsigned NOT NULL COMMENT 'l_ij',
  UNIQUE KEY `idvisitor_seq` (`site_id`,`idvisitor`,`visit_seq`),
  KEY `idvisitor_idx` (`site_id`,`idvisitor`)

CREATE TABLE `tbl_log_visit` (
  `visit_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idsite` int(10) unsigned NOT NULL,
  `idvisitor` binary(8) NOT NULL,
  `visit_last_action_time` DATETIME,
  `config_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`visit_id`),
  KEY `index_idsite_config_datetime` (`site_id`,`config_id`,`visit_last_action_time`),
  KEY `index_idsite_datetime` (`site_id`,`visit_last_action_time`),
  KEY `index_idsite_idvisitor` (`site_id`,`visitor_id`)

CREATE TABLE `tbl_log_conversion` (
  `visit_id` int(10) unsigned NOT NULL,
  `site_id` int(10) unsigned NOT NULL,
  `visitor_id` binary(8) NOT NULL,
  `idgoal` int(10) NOT NULL,
  `idorder` int(10) NOT NULL,
  PRIMARY KEY (`visit_id`,`idgoal`),
  UNIQUE KEY `unique_idsite_idorder` (`site_id`,`idorder`)

Is there some way I can go about improving the performance of this query?

migrated from stackoverflow.com Oct 21 '13 at 21:08

This question came from our site for professional and enthusiast programmers.

  • Actually, the value of purchased is used by the application layer to determine the next value for the purchase counter (which is maintained at that level). The purchased col value returned in the first derived query will only be a 1 or 0. It's simply a flag which is used to update the purchase counter retrieved from the second derived query. – anirvan Oct 7 '13 at 18:23

First of all is goot way to help us to help you is show valid CREATE statements:

tbl_last_input_visit: #1072 - Key column 'idvisitor' doesn't exist in table
tbl_log_visit: #1072 - Key column 'site_id' doesn't exist in table

Second: I will try to find out how to optimize it, but try to check derived queries - it can be the reason of slow processing.

Third: All this queries incompatible: there is no column visit_first_action_time in table tbl_log_visit (which should be named without (_log). Is the field "visit_first_action_time" same type as "visit_last_action_time"?


So the root problem as @Dmitriy mentioned, had to do with the derived queries. Basically, when operating with humongous data sets, derived tables can lead to a whole lot of pain as the underlying indexes from the tables comprising the queries are not available for the derived queries.

In short, if you're writing a SELECT over a derived query over tblA and tblB, then the indexes of tblA and tblB are not available to the derived query. So, if the dataset returned from tblA and tblB is huge, the resulting query will be very slow.

I ended up fixing the solution by breaking apart the derived query into separate queries, and matching the results in the application layer. I also got a sizeable boost in performance by setting up indexes on the columns which were contributing to the GROUP BY clause in one of the queries. (A big thanks to @strawberry for that!)


Personally- I'd create actual, real tables, with indexes etc and then just truncate them prior to running your job.

Temporary tables and similar are nice enough for small things, but for complex things they quickly start to become increasingly slow due to the difficulty for the database engines to optimize the plans plus the memory usage of temporary values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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