Back on topic, I can confirm that MySQL does indeed have various problems with optimizing sub-selects.
There are times where doing two seperate selects is orders of magnitude faster than doing a single with a sub-select due to index selection decisions.
Namely:
select * from table where table_id in (select something from othertable);
is much much slower than:
select something from othertable;
select * from table where table_id in (?, ?, ?, ?, ?, ?, ?, ...)
MySQL are aware of this optimization problem but it does not seem to be of very high priority.
Bad optimization of complex queries is why I am looking at pg.
On 21/01/07, Andrew - Supernews <andrew+nonews@supernews.com> wrote:On 2007-01-21, Ron Johnson <ron.l.johnson@cox.net> wrote:
>> And no BDB (at least last I checked is not GPL)
>
> It's BSD (for obvious reasons), no?
No, Sleepycat's licence is _NOT_ BSD.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend