SQL Optimization case sharing-- Joint Index 04/25 Update SLTechnology News&Howtos

SQL Optimization case sharing-- Joint Index

2025-04-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Here is how to optimize SQL:

Desc select count (*) as total from Art_Person a, Art_Works b where a.PersonCode=b.PersonCode

+-- +

+-- +

| | 1 | SIMPLE | a | ref | PersonCode | PersonCode | 24 | newart.b.PersonCode | 1 | Using index |

+-- +

2 rows in set (0.00 sec)

Mysql > show profile for query 2

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000149 | |

| | checking permissions | 0.000015 | |

| | Opening tables | 0.000049 | |

| | System lock | 0.000032 | |

| | init | 0.000065 | |

| | optimizing | 0.000032 | |

| | statistics | 0.000053 | |

| | preparing | 0.000039 | |

| | executing | 0.000019 | |

| | Sending data | 2.244108 | |

| | end | 0.000042 | |

| | query end | 0.000008 | |

| | closing tables | 0.000023 | |

| | freeing items | 0.000038 | |

| | logging slow query | 0.000007 | |

| | logging slow query | 0.000008 | |

| | cleaning up | 0.000008 | |

+-+ +

18 rows in set (0.00 sec)

Mysql > show create table Art_Works\ G

* * 1. Row *

Table: Art_Works

Create Table: CREATE TABLE `Art_ works` (

`PID`int (11) NOT NULL AUTO_INCREMENT

PRIMARY KEY (`PID`)

KEY `ViewCount` (`ViewCount`)

KEY `PersonCode` (`PersonCode`) USING BTREE

KEY `GoodsStatus` (`GoodsStatus`) USING BTREE

KEY `CreateTime` (`CreateTime`) USING BTREE

KEY `RelWorkID` (`RelWorkID`) USING BTREE

) ENGINE=MyISAM AUTO_INCREMENT=210549 DEFAULT CHARSET=utf8

Mysql > show create table Art_Person\ G

* * 1. Row *

Table: Art_Person

Create Table: CREATE TABLE `Art_ persons` (

`PID`int (11) NOT NULL AUTO_INCREMENT

PRIMARY KEY (`PID`)

UNIQUE KEY `MemberID` (`MemberID`)

KEY `PersonCode` (`PersonCode`) USING BTREE

) ENGINE=MyISAM AUTO_INCREMENT=8699 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Solution (index problem): change it to a federated index with the primary key. When count (), take the primary key and ok it, otherwise you won't leave. In fact, this index is for small tables to drive large tables, but the index of large tables is of no use to count (). Just add the primary key.

Mysql > alter table Art_Person add index idx_PU (PersonCode,PID); with primary key, change to federated index.

Query OK, 8666 rows affected (.49 sec)

Records: 8666 Duplicates: 0 Warnings: 0

Mysql > alter table Art_Works add index idx_PU (PersonCode,PID); with primary key, change to federated index.

Query OK, 166904 rows affected (6.02 sec)

Records: 166904 Duplicates: 0 Warnings: 0

Mysql > desc select sql_no_cache count (*) as total from Art_Works bMagazine person a force index (PersonCode) where b.PersonCode=a.PersonCode

+- -+

+- -+

| | 1 | SIMPLE | b | ref | PersonCode,idx_PU | idx_PU | 25 | newart.a.PersonCode | 1 | Using where; Using index |

+- -+

2 rows in set (0.00 sec)

Here is to delete the index and see how count (1) goes.

Mysql > alter table Art_Person drop index idx_PU

Query OK, 8666 rows affected (.45 sec)

Records: 8666 Duplicates: 0 Warnings: 0

Mysql > alter table Art_Works drop index idx_PU

Query OK, 166904 rows affected (3.90 sec)

Records: 166904 Duplicates: 0 Warnings: 0

Mysql > select sql_no_cache count (1) as total from Art_Works btraining person a force index (PersonCode) where b.PersonCode=a.PersonCode

+-+

| | total |

+-+

| | 166657 |

+-+

1 row in set (2.38 sec)

Mysql > alter table Art_Works add index idx_PU (PersonCode,PID)

Query OK, 166904 rows affected (4.32 sec)

Records: 166904 Duplicates: 0 Warnings: 0

Mysql > select sql_no_cache count (1) as total from Art_Works btraining person a force index (PersonCode) where b.PersonCode=a.PersonCode

+-+

| | total |

+-+

| | 166657 |

+-+

1 row in set (0.44 sec)

Mysql > desc select sql_no_cache count (1) as total from Art_Works btraining person a force index (PersonCode) where b.PersonCode=a.PersonCode

+- -+

+- -+

| | 1 | SIMPLE | b | ref | PersonCode,idx_PU | idx_PU | 25 | newart.a.PersonCode | 1 | Using where; Using index |

+- -+

2 rows in set (0.00 sec)

The following is the index to remove the large table: it is useless to remove count (PersonCode) from the index of the large table, or not to leave the index.

Mysql > alter table Art_Works drop index idx_PU

Query OK, 166904 rows affected (3.82 sec)

Records: 166904 Duplicates: 0 Warnings: 0

Mysql > desc select sql_no_cache count (b.PersonCode) as total from Art_Works bMagazine person a force index (PersonCode) where b.PersonCode=a.PersonCode

+-- +

+-- +

| | 1 | SIMPLE | a | ref | PersonCode | PersonCode | 24 | newart.b.PersonCode | 13 | Using index |

+-- +

2 rows in set (0.00 sec)

Mysql > select sql_no_cache count (b.PersonCode) as total from Art_Works bMagazine person a force index (PersonCode) where b.PersonCode=a.PersonCode

+-+

| | total |

+-+

| | 166657 |

+-+

1 row in set (2.47 sec)

Mysql > alter table Art_Works add index idx_PU (PersonCode,PID)

Query OK, 166904 rows affected (4.23 sec)

Records: 166904 Duplicates: 0 Warnings: 0

Mysql > select sql_no_cache count (b.PersonCode) as total from Art_Works bMagazine person a force index (PersonCode) where b.PersonCode=a.PersonCode

+-+

| | total |

+-+

| | 166657 |

+-+

1 row in set (0.44 sec)

The following are the results of online experiments = =

Mysql > desc select sql_no_cache count (*) as total from Art_Works bMagazine person a force index (PersonCode) where b.PersonCode=a.PersonCode

+-- +

+-- +

| | 1 | SIMPLE | a | ref | PersonCode | PersonCode | 24 | newart.b.PersonCode | 13 | Using index |

+-- +

2 rows in set (0.00 sec)

Mysql > alter table Art_Works add index idx_PU (PersonCode,PID)

Query OK, 173223 rows affected (5.73 sec)

Records: 173223 Duplicates: 0 Warnings: 0

Mysql > desc select sql_no_cache count (*) as total from Art_Works bMagazine person a force index (PersonCode) where b.PersonCode=a.PersonCode

+- -+

+- -+

| | 1 | SIMPLE | b | ref | PersonCode,idx_PU | idx_PU | 25 | newart.a.PersonCode | 1 | Using where; Using index |

+- -+

2 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.