There is a huge difference !!!
When You Load a Table with a Primary Key, the Primary get built automatically.
Not even ALTER TABLE <tbl-name> DISABLE KEYS has an effect since it disables
non-unique indexes. Hence, loading the table is a one-pass operation.
In contrast, loading a table with two non-unique indexes in the way you
specified has the following effect:
Your first statement: create index index_name1 on table_name
(/|index_col_name|/1);
This will make a temp table, copy the data from your table to the temp table,
then build index_name1 on the temp table. Finally it renames the temp table to
your table.
Your second statement: create index index_name2 on table_name
(/|index_col_name|/2);
This will make a temp table, copy the data from your table to the temp table,
then build index_name1 AND index_name2 on the temp table. Finally, it renames
the temp table to your table.
It copies the whole table twice. Notice, it build indexes thrice (3 times) not
twice.
Here is a chart that shows how many index builds would occur it you built
indexes one at a time:
Indexes Temp Table Loads Index Builds
------- ---------------- ------------
1 1 1
2 2 3
3 3 4
4 4 10
5 5 15
6 6 21
7 7 28
8 8 36
9 9 45
10 10 55
11 11 66
12 12 78
13 13 91
14 14 105
15 15 120
16 16 136
17 17 153
N N N(N+1)/2
Building indexes one at a time, and non-unique indexes especially, is very
inefficient. If you have non-unique indexes in your table, disable the keys
first. Here is an example:
CREATE TABLE t1 ( A INT NOT NULL PRIMARY KEY, B INT, C INT );
ALTER TABLE t1 ADD INDEX index_name1 (B);
ALTER TABLE t1 ADD INDEX index_name2 (C);
ALTER TABLE t1 DISABLE KEYS; -- Shuts off non-unique indexes
<Load data into t1> -- Primary Key is Loaded
ALTER TABLE t1 ENABLE KEYS; -- Non-unique indexes are loaded linearly
This is how mysqldumps are reloaded.
Just do a mysqldump of a small table and look at the code it generates for any
one table. Here is a sample mysqldump:
--
-- Table structure for table `contact`
--
DROP TABLE IF EXISTS `contact`;
CREATE TABLE `contact` (
`sno` int(11) NOT NULL auto_increment,
`Name` varchar(50) default NULL,
`mobile` varchar(20) default NULL,
`email` varchar(100) default NULL,
`companyname` varchar(100) default NULL,
`newsletterflag` tinyint(4) default NULL,
`smsflag` tinyint(4) default NULL,
`createdatetime` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`sno`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `contact`
--
LOCK TABLES `contact` WRITE;
/*!40000 ALTER TABLE `contact` DISABLE KEYS */;
INSERT INTO `contact` VALUES (1,'[EMAIL PROTECTED]','6421510248','Srinivasa Rao
Munagala','THL INDIA
',1,NULL,'2007-11-08 21:48:36');
/*!40000 ALTER TABLE `contact` ENABLE KEYS */;
UNLOCK TABLES;
Your best bet is the disable keys, load the data, and enable keys.
Here is a direct quotation from
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in
a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much
faster when you have many indexes.
This feature can be activated explicitly for a MyISAM table. ALTER TABLE ...
DISABLE KEYS tells MySQL to stop updating non-unique indexes. ALTER TABLE ...
ENABLE KEYS then should be used to re-create missing indexes. MySQL does this
with a special algorithm that is much faster than inserting keys one by one, so
disabling keys before performing bulk insert operations should give a
considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX
privilege in addition to the privileges mentioned earlier.
While the non-unique indexes are disabled, they are ignored for statements such
as SELECT and EXPLAIN that otherwise would use them
-----Original Message-----
From: kalin m [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 21, 2008 5:55 PM
To: mysql@lists.mysql.com
Subject: indexes and speeds
hi all...
just wondering what is the performance difference between:
PRIMARY KEY [/|index_type|/] (/|index_col_name|/1,/|index_col_name|/2)
at the time of the table creation
or
create index index_name1 on table_name (/|index_col_name|/1);
create index index_name2 on table_name (/|index_col_name|/2);
after the table has been made?
the question i guess is:
is there performance advantage to have a primary key defined on two (or more)
fields (columns) at the time of table creation or
is it better to have different indexes (keys) defined separately for each
column that needs to be indexed?
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
