List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:May 22 2008 3:21pm
Subject:RE: indexes and speeds
View as plain text  
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,'srini@stripped','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:kalin@stripped]
Sent: Wednesday, May 21, 2008 5:55 PM
To: mysql@stripped
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


Thread
indexes and speedskalin m21 May
  • RE: indexes and speedsRolando Edwards22 May