Developed to see the adjustments I made and found that the index I created was actually created on another test library (Test Library 2), but why is the test library 2 still slow?
So, on the test library 2, I found that the index of the 2 columns I created was already there, and there was one more index I_msource_type:
Indexes ColumnsIndex_type
PRIMARY ext_idUnique
I_mobile Mobile
I_msource_type Msource_type
i_msource_id msource_id
and msource_type column of selectivity is very low, basically here I probably know what is going on, 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= ' xx1391434680574433 ' and msource_type=1) or (mobile= ' 1391434680574433 ' and M source_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 2 indexes, but opted for a very low i_msource_type, which is undoubtedly a less efficient way than a full table scan.
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
Again explain:
Mysql> Explain select ext_id, Mid, msource_id, Msource_type, referee, Mobile, tel, mpassword,status From M_vip_ext where (msource_id= ' xx1391434680574433 ' and msource_type=1) or (mobile= ' 1391434680574433 ' and M source_type=1);
+----+-------------+-----------+-------------+-----------------------+-----------------------+---------+------+ ------+------------------------------------------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-----------+-------------+-----------------------+-----------------------+---------+------+ ------+------------------------------------------------------+
| 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 |
+----+-------------+-----------+-------------+-----------------------+-----------------------+---------+------+ ------+------------------------------------------------------+
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= ' xx1391434680574433 ' and msource_type=1) or (mobile= ' 1391434680574433 ' and M source_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 | 7.328006 |
+-----------------+----------+
1 row in Set (0.00 sec)
OK, the developers use the test Library 2 to do the pressure measurement, reflecting the concurrency can also be 1000.
One SQL optimization for MySQL (ii)
