I'm setting up tables to store details of visitors. One table with details of the visitor session, and another containing the pages they viewed. We're looking at upwards of 4 million records for the visitors table and 20 million for the pages table.
I'd probably be running occasional queries on the tables and obviously I wouldn't want that to slow down the user tracking. I could, if necessary, insert into a temporary table and copy across to the main table at non-peak times?
The default table type is MyISAM. Is this the best table to use or would another be more suitable?
myisam uses a table lock when doing inserts, deletes, or updates
clearly for tables as large as you expect (and the assumed frequency of inserts), this could be a problem
Might InnoDB be better then?
Would it also be faster (albeit take up more disc space) if I convert all VARCHAR fields to CHAR?
yes definitely, and yes but not measurably