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?
Innodb clusters the table data around the primary key... which is what
you're searching on. So your query is able to go right to the spot and
read the whole row, as opposed to myisam which would need to look it up in
the index to find the position in the row, then go read the table itself
to get the row.
among other reasons I suppose.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
