At 06:00 PM 7/16/2002, you wrote: >hello, > >this issue is important for me too. i have a few silly questions > >1. why innodb table type? what is its use? which one is default? (ISAM?)
Innodb Supports row locking and transactions which is very important when a lot of users are updating the same table. MyISAM must do table locking so you will reach a limit quite quickly if 50 or more users are writing to the same table. InnoDb supposedly (according to their website) handles 1200 transactions per second (reads and writes) which is quite remarkable. If you have a readonly table, the default MyISAM table type may be faster to select data. MyISAM supports FullText index, InnoDB does not. With MyISAM you can do a "Select count(*) from Table" and get the results back instantly. InnoDb has to count the rows one by one. With InnoDb you need to pre-allocate file space so all the tables get put into one file. This is a good idea when you have a lot of people accessing the database because it reduces the file handles (less work for the operating system). But it also means all InnoDb databases are put into the same file space. You can't to a quick and dirty backup with InnoDb by shutting down the MySQL sever and copying the database to another directory like you can with MyISAM files (you would be copying all the databases). You need to unload the database data with MySQLDump or an SQL statement. This is a moot point probably but I thought I'd mention it. One additional benefit from using an InnoDb file space is security. Someone isn't going to accidentally delete, modify, or hack into one of your tables using an OS command like MyISAM. They would have to delete the whole InnoDb file space. I personally use all InnoDb tables for a database (I don't mix and match if I don't have to) so I don't have MyISAM files in another directory. I like InnoDb and it is very compatible with MySQL tools. Most of the time I don't even know I'm using InnoDb tables instead of MyISAM tables. >2. i have a table with > > id (int) --> primary key > customer_id (varchar) --> unique > > would it help if i made id as "unique" index as well? or should i make >(id, customer_id) as unique? or all of the above? where can i read more >info about this? The primary key by definition is "unique not null" and should (most always) be a separate (non-compound) key. Your customer_id would be another index. So your indexes look fine the way you have them. The best source would be Paul Dubois MySQL book. It is quite thorough and fun to work through the examples. It does not cover MySQL 4 but has 98% of the stuff you need to know about MySQL. The MySQL manual is also quite a good read (really it is!<g>). The trick is to read either of these from cover to cover, not just the things you're interested in and do the examples one by one. And for more general SQL references visit http://php.weblogs.com/sql_tutorial. Mike --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
