As far as i know, using IN( SUBQUERY ) will give very poor performance,
especially if the record set returned by the large query is really large.
try to use a join instead of WHERE IN( XXX )..
Im not sure why its that much better in INNODB though...
Foo Ji-Haw wrote:
Hi all,
Just want to share and confirm my findings on a performance issue I've
been experiencing.
My database is strictly non-transactional, but it's got about 200,000
records in this particular table. The table has a primary index, and 2
integers - one for the date and the other for the time. Among the
other fields there's a text field which usually stores a huge amount
of text.
One thing I notice, is that under MyISAM running the following sql:
select id from mytable where id in (#subselect to extract a set of ids)
order by mydate desc, mytime desc
The time taken is really bad, like > 90 secs. But in InnoDB it is
usually <8 secs. The time difference is too crazy to ignore.
Can anyone explain this? Is there something in InnoDB that creates the
magic?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
