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

You can see an associated query with three tables in the slow SQL log, as follows:

SELECT COUNT (1) FROM refund_order_item I, artisan a, user u WHERE u.userid = i.user_id AND a.artisan_id = i.artisan_id

Test query time:

Mysql > SELECT COUNT (1)-> FROM refund_order_item I, artisan a, user u-> WHERE u.userid = i.user_id-> AND a.artisan_id = i.artisanSecretiditarianism sec + | COUNT (1) | +-+ | 260605 | +-+ 1 row in set (2.30 sec)

View the execution plan:

Mysql > explain SELECT COUNT (1)-> FROM refund_order_item I, artisan a, user u-> WHERE u.userid = i.user_id-> AND a.artisan_id = i.artisan_id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | + -+-+-- + | 1 | SIMPLE | I | NULL | ALL | idx_user_id Idx_artisan_id | NULL | 255599 | 100.00 | NULL | | 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | hlj.i.artisan_id | 1 | 100.00 | Using index | | 1 | SIMPLE | u | NULL | eq_ref | userid | | userid | 122 | hlj.i.user_id | 1 | 100.00 | Using index | +-| -- +

You can see that the refund_order_item table is not indexed.

Create a federated index:

ALTER TABLE refund_order_item ADD INDEX idx_aid_uid (artisan_id, user_id)

View the execution plan:

Explain SELECT COUNT (1) FROM refund_order_item I, artisan a, user u WHERE u.userid = i.user_id AND a.artisan_id = i.artisan_id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ -+ | 1 | SIMPLE | I | NULL | index | idx_user_id Idx_artisan_id Idx_aid_uid | idx_aid_uid | 244 | NULL | 255599 | 100.00 | Using index | | 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 122 | hlj.i.artisan_id | 1 | 100.00 | Using index | | 1 | SIMPLE | u | NULL | eq _ ref | userid | userid | 122 | hlj.i.user_id | 1 | 100.00 | Using index | + -- +

You can see that the execution plan has been indexed.

Test query time:

Mysql > SELECT COUNT (1)-> FROM refund_order_item I, artisan a, user u-> WHERE u.userid = i.user_id-> AND a.artisan_id = i.artisanonymidity.quli + | COUNT (1) | +-+ | 260605 | +-+ 1 row in set (1.15 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.