Index usage (subquery with MAX)
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
Subject
Views
Written By
Posted
Index usage (subquery with MAX)
2469
May 03, 2005 04:45PM
1688
May 03, 2005 06:36PM
1591
May 04, 2005 09:31AM
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.