Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: query response time

    hi
    i just noticed something strange & i couldn't figure any reason out for it. i have 3 table each one has about 1 million record. every record in every one of tables have a timestamp field, none of them are index.
    i wrote a query like this:
    Code:
    SELECT COUNT(*)AS num, '0' AS row, '0' AS col 
    FROM t0, t1, t2
    WHERE 1=1
    AND t0.id = t1.id
    AND t1.id = t2.id
    AND t2.timestamp BETWEEN 'x' AND 'y'
    GROUP BY '0', '0'
    it's response time in phpMyAdmin is something around 2.5 sec, but when i change timestamp part to "AND t1.timestamp BETWEEN 'x' AND 'y'" (timestamp in t1 or in t0) response time goes up to 22 second!
    i insist there isn't index on timestamp field.
    any idea what's the reason for this huge response time difference?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do an EXPLAIN on each query, paste the results here, and we'll try to, um, explain it to you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    144
    first one with 2 sec res time:
    Code:
    id 	select_type table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1 	SIMPLE 		t2 		ALL 	id 	NULL 		NULL 	NULL 		709875 	Using 	where
    1 	SIMPLE 		t1 		eq_ref 	PRIMARY,id 		PRIMARY 4 			t2.id 	1 	 
    1 	SIMPLE 		t0 		eq_ref 	PRIMARY 		PRIMARY 4 			t1.id 	1 		Using index

    second one with over 20 sec res time:
    Code:
    id 	select_type table 	type 	possible_keys 	key 	key_len ref 	rows 	Extra
    1 	SIMPLE 		t2 		index 	id 				car_id 	4 		NULL 	709875 	Using index
    1 	SIMPLE 		t1 		eq_ref 	PRIMARY,id 		PRIMARY 4 		t2.id 	1 		Using where
    1 	SIMPLE 		t0 		eq_ref 	PRIMARY 		PRIMARY 4 		t1.id 	1 		Using index

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, but i can't figure out why the query that uses the index is slower

    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    looks like now it hits every row in the index and every row in the data page. Not sure why it would be THAT much slower though. I am guessing that t2 is the smallest of the three and that is why it is starting with that table, but no explanation why it would choose the index.

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    There are two possible explanations for this:

    1. The index on timestamp over time will become very inefficient due to the nature of the data which is sequential. A rebuild of the index will help optimize the size of the index and the access into the index.

    2. The x and y values if they do not filter the content to a small subset this could end up being more inefficient with access to the index and subsequent access to the individual data rows. A full table scan in this case would be more efficient.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •