MySQL Forums
Forum List  »  Performance

Optimize a query...
Posted by: Jason Pyeron
Date: April 14, 2005 12:20PM

We have this query which is executing several million times in a row, and I am looking to optimize it.

SELECT * FROM foo AS t1 WHERE a=? AND b=(SELECT MAX(b) FROM foo AS t2 WHERE a=?);

I feel that since EXPLAIN indicates "Select tables optimized away" there must be a more concise/efficient query to do the same query.

Further, it bothers me that a=? is listed twice. Comparing to:

select t1.* from foo as t1, (select a, max(b) as b from foo as t3 where a=? group by a) as t2 where t1.a=t2.a and t1.b=t2.b;

CREATE TABLE foo (
id int NOT NULL PRIMARY KEY auto_increment,
a int NOT NULL,
b int NOT NULL,
INDEX (a),
INDEX (b),
UNIQUE (a,b)
)


mysql> select count(*) from foo;
+----------+
| count(*) |
+----------+
| 66677 |
+----------+
1 row in set (0.04 sec)

mysql> select * from foo where a<100 or a=4381;
+-------+------+----+
| id | a | b |
+-------+------+----+
| 1 | 1 | 3 |
| 2 | 1 | 6 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
| 1852 | 4381 | 0 |
| 4668 | 4381 | 1 |
| 4862 | 4381 | 2 |
| 7422 | 4381 | 3 |
| 9368 | 4381 | 4 |
| 10541 | 4381 | 5 |
| 11383 | 4381 | 6 |
| 21921 | 4381 | 7 |
| 30646 | 4381 | 8 |
| 40062 | 4381 | 9 |
| 55639 | 4381 | 10 |
+-------+------+----+
15 rows in set (0.00 sec)

mysql> explain select * from foo as t1 where a=4381 and b=(select max(b) from foo as t2 where a=4381);
+----+-------------+-------+-------+---------------+------+---------+-------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------------+------+------------------------------+
| 1 | PRIMARY | t1 | const | a,b,a_2 | a | 8 | const,const | 1 | Using index |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+-------+---------------+------+---------+-------------+------+------------------------------+
2 rows in set (0.00 sec)

--
- Jason Pyeron
- Partner & Sr. Manager
- PD Inc. http://www.pdinc.us
- 7 West 24th Street #100
- Baltimore, Maryland 21218

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimize a query...
2175
April 14, 2005 12:20PM


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.