Multi-table Association query optimized by SQL-case 1
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)
