Hi, All.
Help me to understand why such different speed of two queries. These queries do the
same and differ only in superfluous information (marked by red). Speed of the first is
0.37 second. Speed of the latter is 3.95 second.
The first:
SELECT DATE_FORMAT(visits_1.date_time_visit, '%Y-%m-%d') AS dt, Count(DISTINCT
visits_1.id_visitor) AS cnt, visits_1.id_visitor, visits_1.id_visit,
visits_2.id_visitor, visits_2.id_visit, visits_1.visit_number, visits_2.visit_number
FROM visits AS visits_1 INNER JOIN visits AS visits_2 USING(id_visitor)
WHERE visits_1.id_visitor=visits_2.id_visitor
AND visits_1.date_time_visit BETWEEN '2001-05-01' AND '2001-06-30'
AND visits_2.date_time_visit BETWEEN '2001-05-01' AND '2001-06-30'
AND visits_1.visit_number=1
AND visits_2.visit_number=2
AND UNIX_TIMESTAMP(visits_2.date_time_visit)-UNIX_TIMESTAMP(visits_1.date_time_visit)
<= 3600
GROUP BY dt
LIMIT 0,50
The latter:
SELECT DATE_FORMAT(visits_1.date_time_visit, '%Y-%m-%d') AS dt, Count(DISTINCT
visits_1.id_visitor) AS cnt, visits_1.id_visitor, visits_1.id_visit,
visits_2.id_visitor, visits_2.id_visit, visits_1.visit_number, visits_2.visit_number
FROM visits AS visits_1 INNER JOIN visits AS visits_2 USING(id_visitor)
WHERE visits_1.date_time_visit BETWEEN '2001-05-01' AND '2001-06-30'
AND visits_2.date_time_visit BETWEEN '2001-05-01' AND '2001-06-30'
AND visits_1.visit_number=1
AND visits_2.visit_number=2
AND UNIX_TIMESTAMP(visits_2.date_time_visit)-UNIX_TIMESTAMP(visits_1.date_time_visit)
<= 3600
GROUP BY dt
LIMIT 0,50
Best Regards.
Nick.
