One SQL Optimization for Mysql (2) and one SQL Optimization for mysql

One SQL Optimization for Mysql (2) and one SQL Optimization for mysql
After development, I checked my adjustments and found that the index I created was actually created in another test database (Test Database 2). Why is the test database 2 still slow?
So I checked the test database 2 and found that the index of the two columns I created already exists, and I also added an index I _msource_type:
Indexes ColumnsIndex_Type
PRIMARY ext_idUnique
I _mobile mobile
I _msource_type msource_type
I _msource_id msource_id
The selectivity of the msource_type column is very low. Basically, I know what is going on here. The rest is to verify my guess. explain:
Mysql> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword, status from m_vip_ext where (msource_id = 'xx1391421380574433 'and msource_type = 1) or (mobile = '000000' and msource_type = 1 );
+ ---- + ------------- + ----------- + ------ + -------------------------------------- + ---------------- + --------- + ------- + --------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ----------- + ------ + -------------------------------------- + ---------------- + --------- + ------- + --------------- +
| 1 | SIMPLE | m_vip_ext | ref | I _mobile, I _msource_type, I _msource_id | I _msource_type | 4 | const | 58383 | Using where |
+ ---- + ------------- + ----------- + ------ + -------------------------------------- + ---------------- + --------- + ------- + --------------- +
1 row in set (0.17 sec)
Sure enough, the Mysql optimizer did not select the previous two indexes, but chose I _msource_type, which is less efficient than full table scanning.
Decisively drop the index:
Mysql> alter table m_vip_ext drop index I _msource_type;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
Explain again:
Mysql> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword, status from m_vip_ext where (msource_id = 'xx1391421380574433 'and msource_type = 1) or (mobile = '000000' and msource_type = 1 );
+ ---- + ------------- + ----------- + ------------- + ----------------------- + --------- + ------ + Accept +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ----------- + ------------- + ----------------------- + --------- + ------ + Accept +
| 1 | SIMPLE | m_vip_ext | index_merge | I _mobile, I _msource_id | I _msource_id, I _mobile | 98,99 | NULL | 2 | Using sort_union (I _msource_id, I _mobile); Using where |
+ ---- + ------------- + ----------- + ------------- + ----------------------- + --------- + ------ + Accept +
1 row in set (0.17 sec)
Mysql> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword, status from m_vip_ext where (msource_id = 'xx1391421380574433 'and msource_type = 1) or (mobile = '000000' and msource_type = 1) \ G;
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: m_vip_ext
Type: index_merge
Possible_keys: I _mobile, I _msource_id
Key: I _msource_id, I _mobile
Key_len: 98,99
Ref: NULL
Rows: 2
Extra: Using sort_union (I _msource_id, I _mobile); Using where
1 row in set (0.00 sec)


ERROR:
No query specified
Mysql> show status like '% cost % ';
+ ----------------- + ---------- +
| Variable_name | Value |
+ ----------------- + ---------- +
| Last_query_cost | 1, 7.328006 |
+ ----------------- + ---------- +
1 row in set (0.00 sec)
OK. The stress test performed by developers using test database 2 reflects that concurrency can also reach 1000.