* beginner aka ltcmelo > I'm creating a e-commerce app and, consequently, a big > database. I've searched around mysql docs and realizes > that exists some especial (or better) kinda tables.
I suppose you meant "table handlers". > In my small experience i'd choose InnoDB tables for my > database, is this the best option for a e-commerce > database, for example??? All the table handlers can be used for any kind of application. You can also combine different table handlers in the same database and/or application. The default table handler is MyIsam. It is fast, compact and easy to manage. It is a good choice in most cases. It does however have a few weaknesses: It does not support transactions, row level locking, or referential integrity. This could be important, some projects require transactions, a table with very many updates would benefit from row level locking, and it is sometimes a requirement that the database must maintain it's own integrity. InnoDb supports transactions, row level locking and referential integrity. Note that even the InnoDb handler can not maintain full integrity of the database, for instance is the CHECK constraint parsed but ignored. This means you have to check in the application for valid values when you insert new rows, you can not code it into the database. <URL: http://www.mysql.com/doc/en/CREATE_TABLE.html > The InnoDb table handler is probably the best choice when you need transactions (BDB is also an option), when you want foreign key constraint checking and/or when the table is updated very often. "Very often" means like "all the time", for instance when every click on a web page updates some counter, and this is a busy web page. If a MyIsam table is used for this, only one user can update the table at the time, slowing down the site when you have many simultaneous users. Using an InnoDb table multiple updates can be done "at the same time", because of the row level locking. The InnoDb table handler is not allways supported, this spesific task could be solved using MyIsam only, INSERTing each click with a timestamp, pageid, IP and so on. INSERTing to MyIsam tables is very fast, as opposed to UPDATEing. SELECT COUNT(*) FROM TABLE is also very fast using MyIsam, as the record count is stored, it's slower using InnoDb because an index must be scanned. <URL: http://www.mysql.com/doc/en/Table_types.html > <URL: http://www.mysql.com/doc/en/MyISAM.html > <URL: http://www.mysql.com/doc/en/InnoDB.html > MyIsam is the only table handler with support for FULLTEXT indexes, often very usefull, sometimes a must. <URL: http://www.mysql.com/doc/en/Fulltext_Search.html > The MyIsam tables are stored in separate files in the filesystem. This makes them very manageable: you can copy, move & rename tables using OS commands (make sure the server is not in use while you do it). The InnoDb handler stores the table in a single (or a few) data files, you can't backup/copy separate tables using the commands of the OS only. In a future version you will however have the possibility to dictate what tables are stored in which tablespace, this will improve manageability for InnoDb tables. It is easy to change the table handler of an existing table using ALTER TABLE ... TYPE=, so testing combinations of both types in your application could be an option. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
