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.
Best Answer
First add those indexes to the
callstable:Those should make it much faster because they allow the two joins to the
callstable 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.