query response time

bono56 · #1 (**permalink**) 11-20-10, 07:55

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?

r937 · #2 (**permalink**) 11-20-10, 08:02

do an EXPLAIN on each query, paste the results here, and we'll try to, um, explain it to you

bono56 · #3 (**permalink**) 11-20-10, 08:39

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

r937 · #4 (**permalink**) 11-20-10, 09:12

thanks, but i can't figure out why the query that uses the index is slower

good luck

dav1mo · #5 (**permalink**) 11-22-10, 09:49

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.

it-iss.com · #6 (**permalink**) 11-24-10, 15:05

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech