MySQL Forums
Forum List  »  Performance

Index usage (subquery with MAX)
Posted by: Felix Geerinckx
Date: May 03, 2005 04:45PM

I have the following table

CREATE TABLE foo (
name VARCHAR(100) NOT NULL,
time BIGINT NOT NULL,
data VARCHAR(200)
) Engine = MyISAM;
ALTER TABLE foo ADD INDEX (name);
ALTER TABLE foo ADD INDEX (time);

filled with 5000 records.
name comes random from 20 different names.
time is unixtime for dates in 2005.
data is bogus data (in fact a concat from name and time)

The so-called inefficient MAX-CONCAT method:

EXPLAIN SELECT
SUBSTRING(MAX(CONCAT(LPAD(time,20,'0'), data)), 21)
FROM foo
GROUP BY foo.name;

returns

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5000
Extra: Using temporary; Using filesort

and takes 0.08 sec to execute.

The textbook method:

EXPLAIN SELECT
f1.data
FROM foo AS f1
WHERE
f1.time=(SELECT MAX(f2.time) FROM foo AS f2 WHERE f2.name = f1.name)
GROUP BY f1.name;

returns

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: f1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5000
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: f2
type: ref
possible_keys: name
key: name
key_len: 100
ref: test.f1.name
rows: 504
Extra:

and takes 15.72 sec to run.

What's happening?

PS: SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 4.1.7-nt |
+-----------+


--
felix

Options: ReplyQuote


Subject
Views
Written By
Posted
Index usage (subquery with MAX)
2347
May 03, 2005 04:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.