These days, I started to use mysql to query table with million rows. In order to get to know mysql performance better. I did this test. Basically, I created different tables with different type/number of indices. I tried to insert as many rows as possible within a certain time. And see the result.
Environment:
Windows 8.1
Memory 12GB
5.1.73-community
CPU AMD A10, 2.1G
index table1(no index)
CREATE TABLE `itable1` (
`Id1` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’insertion test, no index’
index table2(1 int index)
CREATE TABLE `itable2` (
`Id1` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL,
KEY `idx1` (`Id1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’insertion test, 1 int index’
index table3(2 int indices)
CREATE TABLE `itable3` (
`Id1` int(11) NOT NULL DEFAULT ‘0’,
`id2` int(11) DEFAULT NULL,
KEY `idx1` (`Id1`),
KEY `idx2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’insertion test, 2 int indices’
index table4(10 int indices)
CREATE TABLE `itable4` (
`Id1` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL,
`id3` int(11) DEFAULT NULL,
`id4` int(11) DEFAULT NULL,
`id5` int(11) DEFAULT NULL,
`id6` int(11) DEFAULT NULL,
`id7` int(11) DEFAULT NULL,
`id8` int(11) DEFAULT NULL,
`id9` int(11) DEFAULT NULL,
`id10` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’insertion test, 10 int indices’
index table5(2 varchar indices)
CREATE TABLE `itable5` (
`Id1` varchar(255) DEFAULT NULL,
`id2` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’insertion test, 2 varchar indices’
I run for 170 seconds, and the result is below:
Conclusion:
1. The more indices a table has, the slower the insertion does.
2. The more indices a table has, the more space it will take up.
*3. Index table insertion speed is constant as number of row increases.
3* beyong my expectation. Mysql uses B+ tree. For one row insertion, it takes logN time. Continously inserting N rows takes log1 + log2 + … + logN = logN! time. logN! is approximately NlogN. So I expected it is going to be a curve, not linear. Werid…
For my java code, please click here: https://www.github.com/allenlipeng47/MillionTableTest
I think the 2 tables below can generally gives us a feeling about index time / space factor.