Hey there Ady, Philip,
Thanks for the suggestions for the phenomenon. I also notice something
along the course of optimisation:
1. Sorting records with huge fields (ie: blobs, text) is significantly
slower than if you extract the blobs/ text fields into a separate table.
The record size makes a difference in the sorting performance?
2. Sorting by int desc, int desc is much slower than int asc, int asc,
where 'int' is an integer field.
If (1) is generally true, perhaps to accommodate the potential increase
of the table into a million records, I have to break the table into 2
tables: the original table will hold only enough fields for sorting, and
the huge fields in the other.
What do you guys think?
Ady Wicaksono wrote:
Hi Foo,
MyISAM impress me on insert speed, however on many case MyISAM is not
better than Innodb. If you can't use combination of them,
better your break down your need to decide which one to use. AFAIK,
sub query is better in innodb rather than myisam, and if you have only
200.000 records
with huge amount of text, innodb is good enough, just make sure you
have enough memory to increase performance. Do you need fulltext
SEARCH? If yes, myisam is support this :D not innodb.
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]
