A test in mysql where index misuse is slower than without indexing
- 2020-05-12 06:17:37
- OfStack
Here is the data we inserted into the tuangou table:
id web city type
1 lashou network Beijing food and beverage
Lashou net Shanghai leisure and entertainment
Three hundred group tianjin food and beverage
Lashou net shenzhen online shopping
Shijiazhuang discount ticket volume
Six hundred group handan beauty and health care
..
Chongqing tourist hotel
5000 lashou net xi 'an coupons
Execute mysql statement:
$sql = "select from tuangou where web = 'lashou net' and city = 'Shanghai'";
(1) if no index is added, the execution time is: 0.0041 seconds
(2) if only web is indexed, the execution time is 0.0064 seconds
(3) if both web and city are indexed, the execution time is 0.0007 seconds
In (2), the index is added, but the query time is more than the unindexed time. So I recommend that you index the fields that you need to query frequently.
id web city type
1 lashou network Beijing food and beverage
Lashou net Shanghai leisure and entertainment
Three hundred group tianjin food and beverage
Lashou net shenzhen online shopping
Shijiazhuang discount ticket volume
Six hundred group handan beauty and health care
..
Chongqing tourist hotel
5000 lashou net xi 'an coupons
Execute mysql statement:
$sql = "select from tuangou where web = 'lashou net' and city = 'Shanghai'";
(1) if no index is added, the execution time is: 0.0041 seconds
(2) if only web is indexed, the execution time is 0.0064 seconds
(3) if both web and city are indexed, the execution time is 0.0007 seconds
In (2), the index is added, but the query time is more than the unindexed time. So I recommend that you index the fields that you need to query frequently.