From: "Kelvin Wu"
> 1, If I created table 'newtest' using InnoDB type, inserting 50k
> records took me 20min! If I use MyISAM type, inserting took me less
> then 1 min. I don't know much about InnoDB but how come the
> performance is dropped down so much?
Do read everything in the MySQL manual and on the InnoDB website about the
InnoDB engine. There are many differences between MyISAM and InnoDB; a few:
- MyISAM supports FULL TEXT indexes, InnoDB doesn't
- InnoDB supports transactions, MyISAM doesn't
- MyISAM excels in situations where there are either many updates/inserts
and few selects, or many selects and few inserts/updates ("low concurrency")
- InnoDB excels in situations where there are many updates/inserts and many
selects ("high concurrency")Inserting 50k records is something MyISAM is very comfortable with. In some cases it is faster to drop the indexes, insert the records and recreate the indexes (especially with full text indexes). We use InnoDB in a situation where there are lots of inserts, updates and selects. Tests we've done show that MyISAM is a lot faster when there are very few records (< 50k), but the average execution time for a query increases almost lineary with the number of records, while InnoDB shows almost constant query execution times for very small and large tables. > 2, Even I choose MyISAM type, the same perl script has another huge > performance difference on 'mytest' which is copied from MySQL 3 and > 'newtest' which is created under MySQL 4. > > query is simple too, something like: > SELECT id, sessionid, timestamp FROM newtest ORDER BY sessionid DESC > LIMIT 0, 100; > > If I didn't use ORDER BY, or use ORDER BY primary key id, performance > is same on these two tables, if I use ORDER BY (sessionid or > timestamp), 'mytest' is 10 times faster than 'newtest'. > > Did I do anything wrong? Anyone can help on this? A lot of things could have happened. For test purposes use SELECT SQL_NO_CACHE id, ses..... to disable the query cache in MySQL 4+. If you altered the newtest table the query cache is empty, while mytest can get the results from the cache straight away (hugh performance difference). An order by on timestamp will use a full table scan and is thus rather slow (all records must be read to get the result). Is the output from EXPLAIN SELECT id...... different for both tables? It should show you which decisions MySQL made while analysing the query. 10 times seems to be a lot, but what kind of execution times are we talking about here? 0.0005 vs. 0.005 or 2 vs. 20 seconds? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
