Optimize a query...
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