2

I am trying to pull calls associated with website visits from our huge database... I have got SQL that works, pulls correct results it just takes over minute to complete...

Here is the SQL

SELECT SourceMediumID, 
       CONCAT(SourceDescription, " / ", MediumDescription),
       COUNT(DISTINCT first_click_calls.CallID) AS 'First Click Calls',
       COUNT(DISTINCT first_click_visitors.VisitorID) AS 'First Click Visitors',
       COUNT(DISTINCT last_click_calls.CallID) AS 'Last Click Calls',
       COUNT(DISTINCT last_click_visitors.VisitorID) AS 'Last Click Visitors'             
FROM sourcemediumreference
LEFT JOIN sources ON SourceMediumSourceID = SourceID
LEFT JOIN mediums ON SourceMediumMediumID = MediumID

LEFT JOIN calls last_click_calls ON last_click_calls.CallSourceMediumID = SourceMediumID 
                                 AND last_click_calls.CallCampaignID = 222 
                                 AND last_click_calls.CallDate >= '2015-03-01' 
                                 AND last_click_calls.CallDate <= '2015-03-31'
LEFT JOIN visitorvisits last_click_visits ON last_click_calls.CallVisitID = last_click_visits.VisitID
LEFT JOIN visitorvisitors last_click_visitors ON last_click_visits.VlviVisitorID = last_click_visitors.VisitorID

LEFT JOIN calls first_click_calls ON first_click_calls.CallFirstSourceMediumID = SourceMediumID 
                                  AND first_click_calls.CallCampaignID = 222 
                                  AND first_click_calls.CallDate >= '2015-03-01' 
                                  AND first_click_calls.CallDate <= '2015-03-31'
LEFT JOIN visitorvisits first_click_visits ON first_click_calls.CallFirstVisitID = first_click_visits.VisitID
LEFT JOIN visitorvisitors first_click_visitors ON first_click_visits.VlviVisitorID = first_click_visitors.VisitorID

WHERE SourceGenericFlag=1 OR SourceCustomerID=249

GROUP BY SourceMediumID

And These are the results:

+--------+-------------------------------------------------+-------------------+----------------------+------------------+---------------------+
| SomeID | SourceMedium                                    | First Click Calls | First Click Visitors | Last Click Calls | Last Click Visitors |
+--------+-------------------------------------------------+-------------------+----------------------+------------------+---------------------+
|      1 | google / organic                                |               175 |                  144 |              307 |                 100 |
|      2 | google / ppc                                    |                 0 |                    0 |               28 |                   0 |
|      3 | direct / direct                                 |               206 |                  170 |              609 |                 217 |
|      4 | about / organic and ppc                         |                 0 |                    0 |                0 |                   0 |
|      5 | alltheweb / organic and ppc                     |                 0 |                    0 |                0 |                   0 |
|      6 | alta vista / organic and ppc                    |                 0 |                    0 |                0 |                   0 |
|      8 | aol / organic and ppc                           |                 5 |                    4 |                5 |                   4 |
|      9 | ask / organic and ppc                           |                 3 |                    3 |                2 |                   2 |
|     11 | bing / organic and ppc                          |               334 |                  232 |              577 |                 243 |
|     12 | lycos / organic and ppc                         |                 0 |                    0 |                0 |                   0 |
|     13 | yahoo / organic                                 |                19 |                   18 |              144 |                  18 |
|     14 | yahoo / ppc                                     |                 0 |                    0 |                0 |                   0 |
|     68 | referral / referral                             |               183 |                  160 |             1393 |                 186 |
|    109 | Source 1   / referral                           |                66 |                   50 |               49 |                  40 |
|    110 | Source 2             / referral                 |                 0 |                    0 |                0 |                   0 |
|    315 | TEST                    / referral              |                11 |                   10 |               12 |                  10 |
|    324 |     Website / direct                            |                 0 |                    0 |                0 |                   0 |
|    325 | test  .co.uk / referral                         |                 0 |                    0 |                0 |                   0 |
|    338 |         Dealers / referral                      |                 0 |                    0 |                0 |                   0 |
|    339 | omg.com / referral                              |                 0 |                    0 |                0 |                   0 |
|    340 |        Heads / referral                         |                 4 |                    3 |                2 |                   2 |
|    352 | Yellow Pages / referral                         |                 0 |                    0 |                0 |                   0 |
|    367 | PPC / ppc                                       |               232 |                  191 |              185 |                 154 |
|    376 | PPC / organic                                   |              1246 |                 1003 |             1275 |                1054 |
+--------+-------------------------------------------------+-------------------+----------------------+------------------+---------------------+
24 rows in set (1 min 17.64 sec)

When I do "EXPLAIN" it's telling me that it's using these indexes:

table: sources | type: index_merge | index: index_SourceGenericFlag,index_SourceCustomerID
table: sourcemediumreference| type: ref | index: index_SourceMediumSourceID
table: last_click_calls | type: ref | index: index_CallCampaignID 
table: first_click_calls | type: ref | index: index_CallCampaignID

Here is full result of EXPLAIN:

+----+-------------+-----------------------+-------------+------------------------------------------------------+------------------------------------+---------+--------------------------------------------+-------+------------------------
-----------------------------------------------------------------------+
| id | select_type | table                 | type        | possible_keys                                        | key                                | key_len | ref                                        | rows  | Extra
                                                                       |
+----+-------------+-----------------------+-------------+------------------------------------------------------+------------------------------------+---------+--------------------------------------------+-------+------------------------
-----------------------------------------------------------------------+
|  1 | SIMPLE      | sources               | index_merge | PRIMARY,index_SourceCustomerID,index_SourceGenericFlag           | index_SourceGenericFlag,index_SourceCustomerID | 4,4     | NULL                                       |    20 | Using union(index_SourceGen
ericFlag,index_SourceCustomerID); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | sourcemediumreference | ref         | index_SourceMediumSourceID                                       | index_SourceMediumSourceID                      | 4       | mydbtest.sources.SourceID                    |     1 |
                                                                       |
|  1 | SIMPLE      | mediums               | eq_ref      | PRIMARY                                              | PRIMARY                            | 4       | mydbtest.sourcemediumreference.SourceMediumMediumID  |     1 |
                                                                       |
|  1 | SIMPLE      | last_click_calls      | ref         | index_CallDate ,index_CallSourceMediumID,index_CallCampaignID       | index_CallCampaignID                      | 4       | const                                      | 78917 |
                                                                       |
|  1 | SIMPLE      | last_click_visits     | eq_ref      | PRIMARY                                              | PRIMARY                            | 4       | mydbtest.last_click_calls.CallVisitID       |     1 |
                                                                       |
|  1 | SIMPLE      | last_click_visitors   | eq_ref      | PRIMARY                                              | PRIMARY                            | 4       | mydbtest.last_click_visits.VlviVisitorID      |     1 | Using index
                                                                       |
|  1 | SIMPLE      | first_click_calls     | ref         | index_CallDate ,index_CallCampaignID ,index_CallFirstSourceMediumID | index_CallCampaignID                      | 4       | const                                      | 78917 |
                                                                       |
|  1 | SIMPLE      | first_click_visits    | eq_ref      | PRIMARY                                              | PRIMARY                            | 4       | mydbtest.first_click_calls.CallFirstVisitID |     1 |
                                                                       |
|  1 | SIMPLE      | first_click_visitors  | eq_ref      | PRIMARY                                              | PRIMARY                            | 4       | mydbtest.first_click_visits.VlviVisitorID     |     1 | Using index
                                                                       |
+----+-------------+-----------------------+-------------+------------------------------------------------------+------------------------------------+---------+--------------------------------------------+-------+------------------------
-----------------------------------------------------------------------+

last_click_calls and first_click_calls are alliases for calls table. I am joining calls twice because it needs to be joined on two different fields : CallSourceMediumID AND CallFirstSourceMediumID

LEFT JOIN calls last_click_calls ON last_click_calls.CallSourceMediumID = SourceMediumID
LEFT JOIN calls first_click_calls ON first_click_calls.CallFirstSourceMediumID = SourceMediumID 

Here are results on show create table for calls table:

| calls | CREATE TABLE `calls` (
  `CallID` int(11) NOT NULL AUTO_INCREMENT,
  `CallVisitID` int(11) NOT NULL,
  `CallCampaignID` int(11) NOT NULL,
  `CallSourceMediumID` int(11) NOT NULL,
  `CallDate` date NOT NULL DEFAULT '0000-00-00',
  `CallFirstSourceMediumID` int(11) NOT NULL,
  `CallFirstVisitID` int(11) NOT NULL,
  PRIMARY KEY (`CallID`),
  KEY `idx_CallDate` (`CallDate`),
  KEY `idx_CallVisitID` (`CallVisitID`),
  KEY `idx_CallSourceMediumID` (`CallSourceMediumID`),
  KEY `idx_CallCampaignID` (`CallCampaignID`),
  KEY `idx_CallFirstVisitID` (`CallFirstVisitID`),
  KEY `idx_CallFirstSourceMediumID` (`CallFirstSourceMediumID`)
) ENGINE=MyISAM AUTO_INCREMENT=36783127 DEFAULT CHARSET=latin1 |

I am using MySQL 5.1 MyISAM engine.

Can you guys see anything I could do to speed it up?

Any help will be appreciated.

1

First add those indexes to the calls table:

(CallCampaignID, CallSourceMediumID, CallDate)
(CallCampaignID, CallFirstSourceMediumID, CallDate)

Those should make it much faster because they allow the two joins to the calls table to check substantially less rows (it is ~70k each time now, which means millions of combinations).

After that check the performance and add the new EXPLAIN. There might be some ways to make it even faster, but probably not by as much.

| improve this answer | |
1

Composite indexes.

LEFT JOIN calls last
                    ON last.CallSourceMediumID = SourceMediumID 
                   AND last.CallCampaignID = 222 
                   AND last.CallDate >= '2015-03-01' 
                   AND last.CallDate <= '2015-03-31'

needs

INDEX(CallSourceMediumID, CallCampaignID, CallDate)

(The first two columns can be in either order, but the Date must be at the end because of being a range.)

Suggest this pattern:

                   AND last.CallDate >= '2015-03-01'
                   AND last.CallDate  < '2015-03-01' + INTERVAL 1 MONTH

Similarly for the other JOIN to calls.

Keep up the pressure to move to InnoDB and move past 5.1. Lots of optimization improvements were put into 5.6. And, the longer you wait to upgrade, the harder it will be.

Another thing to try:

Instead of

COUNT(DISTINCT x.y) AS z
...
LEFT JOIN x ON ...

try

( SELECT COUNT(DISTINCT y) FROM x WHERE ... ) AS z

In some situations (I don't know about yours), it could eliminate the need for the GROUP BY. Often the GROUP BY is to shrink the result set after it was bloated by JOINs. This may be a huge speedup.

| improve this answer | |
  • Hi, I got the index, I can't do '+ INTERVAL 1 MONTH' as the dates are user input, and cant really do "( SELECT COUNT(DISTINCT y) FROM x WHERE ... ) AS z" as different customer will have different sources/mediums – zachu Feb 1 '16 at 11:09
0

I have tried all thing you guys suggested. Thanks for that, it was useful but did not solve my problem. Indexes suggested by jkavalik helped a little bit, but the query was still too slow (45 seconds~). The problem was that there was 2 large tables - visits and calls. And I had to join them twice in the query. It was taking long time. After few days of trying to improve the query with no luck, I decided to re-structure tables. I have created "summary table", basically calls table with some extra fields from visits and visitors tables. That way I don't have to join visits/visitors tables and the query speed up. It was taking about 15 seconds to complete. I was happy about the time, but still wanted to improve it. I have created covering index for the query, and now it comes back in 0.5 second ! Which I am very happy with :)

Thank you for all the help, especially jkavalik.

| improve this answer | |

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.