Bookmark and Share
An example of using TEMPORARY TABLE
(Publish Date: 2009-6-3 10:53am, Total Visits: 7210, Today: 2, This Week: 2, This Month: 6)
One query:
 
It takes more than 4 minutes to excute the following query. After using the TEMPORARY TABLE, it reduces to 30s.
 
mysql> explain select se_09.sweep_id,se_09.first_name,se_09.last_name,se_09.address,
    -> se_09.address2,se_09.city,se_09.state,se_09.postal_code,se_09.country_code,
    -> se_09.email,se_09.dob,se_09.creation_date, tr.trans_id
    -> from transactions tr, site_info s, sweepstakes_entry_09 se_09
    -> where tr.creation_date >= '2009-05-17'
    -> and tr.creation_date < '2009-05-24'
    -> and tr.se_id is not null
    -> and tr.trans_status = 'Declined'
    -> and s.auto_registration_status = 'Inactive'
    -> and tr.site_id = s.site_id
    -> and se_09.trans_id = tr.trans_id order by se_09.trans_id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: se_09
         type: ALL
possible_keys: sweepstakes_entry_fkey1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14988777
        Extra: Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tr
         type: eq_ref
possible_keys: PRIMARY,AI_trans_id,site_id_2,se_id_2,transactions_fkey7,transactions_fkey9,creation_date,trans_status
          key: PRIMARY
      key_len: 4
          ref: hearst.se_09.trans_id
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: hearst.tr.site_id
         rows: 1
        Extra: Using where
3 rows in set (0.03 sec)
 
Using temp table:
 
 
mysql> CREATE TEMPORARY TABLE testtmp select * from transactions tr
    -> where tr.creation_date >= '2009-05-17'
    -> and tr.creation_date < '2009-05-24';
Query OK, 340937 rows affected, 2 warnings (21.88 sec)
Records: 340937  Duplicates: 0  Warnings: 2
(creation_date is indexed on the table transactions.)
 
mysql> explain select se_09.sweep_id,se_09.first_name,se_09.last_name,se_09.address,
    -> se_09.address2,se_09.city,se_09.state,se_09.postal_code,se_09.country_code,
    -> se_09.email,se_09.dob,se_09.creation_date, tr.trans_id
    -> from testtmp tr, site_info s, sweepstakes_entry_09 se_09
    -> where tr.se_id is not null
    -> and tr.trans_status = 'Declined'
    -> and s.auto_registration_status = 'Inactive'
    -> and tr.site_id = s.site_id
    -> and se_09.trans_id = tr.trans_id order by se_09.trans_id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tr
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 341628
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: s
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: hearst.tr.site_id
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: se_09
         type: ref
possible_keys: sweepstakes_entry_fkey1
          key: sweepstakes_entry_fkey1
      key_len: 4
          ref: hearst.tr.trans_id
         rows: 86
        Extra:
3 rows in set (0.01 sec)
 
ERROR:
No query specified
=======attach tables' definition==================