Brent,
I tried this and it definitely boosted performance. On a test query
that would take 2+ seconds to run with 20 id's - it ran in 0.002
seconds.
Thanks everyone for your help and comments.
Erik
On Sep 21, 2007, at 2:01 PM, Brent Baisley wrote:
As others have mentioned, mysql doesn't
As others have mentioned, mysql doesn't handle IN queries efficiently.
You can try changing it to using derived tables/subqueries. I did some
quick tests and the explain shows a different analysis.
select comment, gid, date_posted from tbl
JOIN
(select max(id) as mid
from tbl where gid in ( 1234,2
Erik,
Even is you eliminate the subquery (which I have used efficiently in
the past, but they are always something to be careful of), the IN
clause is going to kill you.. above a certain number of elements in
that clause, the optimizer will go straight to full table scan. This
drawback is well-k
Erik, I think the main reason your query is running slowly is the use of a
subselect. MySQL does not generally perform well with subselects, though
work continues in that area.
There is also a problem/situation in MySQL in that you can't use MAX/GROUP
BY functions quite the way you can in other d
Hello everyone,
The app server in this case is PHP, and the database is MySQL 5.0.22
on RedHat linux
I've got a database with about 7.5K records in it that I expect to
start growing very quickly ~10-12K records per day. The storage
engine is InnoDB. This table is growing quickly and will
