Join mysticism

2001-06-26 Thread Nick Kostirya 

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.

RE: Join mysticism

2001-06-26 Thread Bruce Stewart 

Nick,

What happens to the time if you run the following on your database:
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,
  visits AS visits_2
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_v
isit) = 3600
GROUP BY dt
LIMIT 0,50

Just interested to know,
Bruce

-Original Message-
From: Nick Kostirya [mailto:[EMAIL PROTECTED]]
Sent: Tue, 26 June 2001 08:39
To: [EMAIL PROTECTED]
Subject: Join mysticism


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_v
isit) = 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_v
isit) = 3600
GROUP BY dt
LIMIT 0,50

Best Regards.
Nick.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php