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 have 2 big tables and 1 small table. Each big table has 100M items. The small table has 1M items. I tested select, join operations among them indexed filed or no indexed field.
Environment:
Windows 8.1
Memory 12GB
5.1.73-community
CPU AMD A10, 2.1G
big table1:
CREATE TABLE `btable1` (
`indexId1` int(11) NOT NULL AUTO_INCREMENT,
`indexId2` int(11) DEFAULT NULL,
`unIndexVarchar` varchar(255) DEFAULT NULL,
PRIMARY KEY (`indexId1`),
KEY `indexId2` (`indexId2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=’100 million rows, 2 indices field’
big table 2:
CREATE TABLE `btable2` (
`indexId` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘indexed primary key’,
`unIndexId` int(11) DEFAULT NULL,
`unIndexVarchar` blob,
PRIMARY KEY (`indexId1`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=’100 million rows, 1 index field’
small table:
CREATE TABLE `stable` (
`indexId` int(11) NOT NULL AUTO_INCREMENT,
`unIndexId` int(11) DEFAULT NULL,
`unIndexVarchar` blob,
PRIMARY KEY (`indexId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=’small table, has 1 million items’
Then, I did the following test among these tables:
1. SmallTable count
select count(*) from stable
result: 1M; time: 2s
2. BigTable count
select count(*) from btable1
result: 100M; time: 110s
3. BigTable join SmallTable.
select count(*) from btable1 join stable on btable1.indexId1 = stable.unIndexId
result: 1M; time: 9.11s
4. BigTable1 join SmallTable join BigTable2
select count(*) from btable1 join stable on btable1.indexId1 = stable.indexId join btable2 on btable2.unIndexId = stable.indexId
result: 1M; time: 14s
5. BigTable1 join BigTable2 join SmallTable
select count(*) from btable1 join btable2 on btable1.indexId1 = btable2.indexId join stable on btable1.indexId1 = stable.indexId
result: 1M; time: 8.8s
6. SmallTable count, unindex condition
select count(*) from stable where stable.unIndexVarchar=’AK’
result: 1451, time: 2.9s
7. BigTable join SmallTable, unindex condition
select count(*) from btable1 join stable on btable1.indexId1 = stable.indexId where stable.unIndexVarchar=’KA’
result: 1471, time: 3s
8. BigTable join SmallTable, unindex condition
select count(*) from btable1 join stable on btable1.indexId1 = stable.indexId where btable1.unIndexVarchar=’IO’
result: 1543, time: 4.3s
9. BigTable join SmallTable, unindex condition
select count(*) from btable1 join stable on btable1.indexId1 = stable.unIndexId where stable.unIndexVarchar=’AK’
Consolution:
2, 3 shows if a big table join a small table, it will be efficient than counting a big table.
3, 4 shows if a small table joins, and the result is less, it will help to break the big table.
4, 5 shows the join order doesn’t matter, mysql can do a optimizatino on the sql query.
1, 6 shows that these 2 queries has no big difference. They all have O(n) time.
7, 8 shows query with condition on small table will be efficient than condition on a big table.
9 takes longer time than 3. It shows if a condition is without index, it will be inefficient.