The development reaction does not go on when the pressure measurement, there is a SQL execution very slow, so explain:
MariaDB [db_vip]>
MariaDB [db_vip]> Explain select ext_id, Mid, msource_id, Msource_type, referee, Mobile, tel, mpassword,stat us from M_DB_VIP where (msource_id= ' xx1391434680574433 ' and msource_type=1) or (mobile= ' 139143468057443 3 ' and msource_type=1);
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| 1 | Simple | M_DB_VIP | All | NULL | NULL | NULL | NULL | 86987 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in Set (0.00 sec)
MariaDB [db_vip]> Show status like '%cost% ';
+-----------------+--------------+
| variable_name | Value |
+-----------------+--------------+
| Last_query_cost | 18006.399000 |
+-----------------+--------------+
1 row in Set (0.00 sec)
As can be seen from the execution plan, this SQL has a full table scan of table M_DB_VIP, while table M_DB_VIP has about 100,000 rows, and the MySQL optimizer evaluates the cost at 18006.
In the three columns of the Where condition, msource_id and mobile are highly selective columns, and the msource_type is weak in selectivity.
First create an index on the column msource_id:
MariaDB [db_vip]> CREATE index i_m_db_vip_1 on M_DB_VIP (msource_id);
Query OK, 0 rows affected (1.18 sec)
records:0 duplicates:0 warnings:0
Execute SQL again and find that MySQL does not use the MSOURCE_ID index (or Oracle's CBO intelligence!). ）
MariaDB [db_vip]> Explain select ext_id, Mid, msource_id, Msource_type, referee, Mobile, tel, mpassword,stat us from M_DB_VIP where (msource_id= ' xx1391434680574433 ' and msource_type=1) or (mobile= ' 139143468057443 3 ' and msource_type=1);
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| 1 | Simple | M_DB_VIP | All | I_m_db_vip_1 | NULL | NULL | NULL | 86987 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in Set (0.01 sec)
MariaDB [db_vip]> Show status like '%cost% ';
+-----------------+--------------+
| variable_name | Value |
+-----------------+--------------+
| Last_query_cost | 18006.399000 |
+-----------------+--------------+
1 row in Set (0.00 sec)
Then create an index on the mobile:
MariaDB [db_vip]> CREATE index i_m_db_vip_2 on M_DB_VIP (mobile);
Query OK, 0 rows affected (0.96 sec)
records:0 duplicates:0 warnings:0
MariaDB [db_vip]> Explain select ext_id, Mid, msource_id, Msource_type, referee, Mobile, tel, mpassword,stat us from M_DB_VIP where (msource_id= ' xx1391434680574433 ' and msource_type=1) or (mobile= ' 139143468057443 3 ' and msource_type=1);
+------+-------------+-----------+-------------+-----------------------------+-----------------------------+--- ------+------+------+-------------------------------------------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+------+-------------+-----------+-------------+-----------------------------+-----------------------------+--- ------+------+------+-------------------------------------------------------+
| 1 | Simple | M_DB_VIP | Index_merge | i_m_db_vip_1,i_m_db_vip_2 | i_m_db_vip_1,i_m_db_vip_2 | 98,99 | NULL | 2 | Using Union (i_m_db_vip_1,i_m_db_vip_2); Using where |
+------+-------------+-----------+-------------+-----------------------------+-----------------------------+--- ------+------+------+-------------------------------------------------------+
1 row in Set (0.02 sec)
MariaDB [db_vip]> Show status like '%cost% ';
+-----------------+----------+
| variable_name | Value |
+-----------------+----------+
| Last_query_cost | 6.826060 |
+-----------------+----------+
1 row in Set (0.00 sec)
MariaDB [db_vip]>
The execution cost of SQL has been greatly reduced, the development has been tested, the effect is quite good, concurrent from 10 to more than 2000.
One SQL optimization for MySQL (i)
