This is the 25th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Conditional field function operation

In SQL statements, the order of index values can be broken when performing functional operations on index fields, so the optimizer decides to abandon the tree-searching feature. This is not to say that MySQL has abandoned the use of indexes, but rather abandoned the tree search function of indexes and used only full index scans.

For example, SQL checks the transaction data in July every year

select count(*) from tradeLog where month(createTime)=7;
Copy the code

Use the Explain statement to view the execution and find that although indexes are used, the number of rows scanned is close to full table data, meaning that all values of the entire index are taken.

The solution is to change the range query in SQL. For example,

mysql> select count(*) from tradeLog where
    -> (createTime >= '2016-7-1' and createTime<'2016-8-1') or
    -> (createTime >= '2017-7-1' and createTime<'2017-8-1') or
    -> (createTime >= '2018-7-1' and createTime<'2018-8-1');
Copy the code

Sometimes SQL statements do not change order and the optimizer does not consider using indexes. For example,

select * from tradeLog where id+1=10000;
Copy the code

To solve this problem, manually change the id to 10000-1.

Implicit type conversion

mysql> select * from tradeLog where tradeid=1007;
Copy the code

Tradeid is the index and the type is varchar(32). Since the input parameter is an integer, type conversion is required. To the optimizer, this SQL statement is equivalent to

mysql> select * from tradeLog where CAST(tradid AS signed int) = 110717;
Copy the code

In other words, this statement triggers the rule we mentioned above: the optimizer disallows tree-searching by performing a function on an index field.

Here’s a simple way to see what the rules are for type conversions:

  • If the rule is “convert string to number”, then it is a number comparison and the result should be 1;
  • If the rule is “convert a number to a string”, then you are doing a string comparison and the result should be 0.

Select * from ’10’ where ‘9’ = ‘1’; The traDID field is converted to an integer for comparison.

Implicit character encoding conversion

When performing a linked table query, if the two tables corresponding to the associated field have different character sets, the field may be converted into a character set. In this case, the internal function is used for character set conversion. In this case, the optimizer will give up the function of tree-searching.

Note that the character set is only one of the conditions, the join process requires that the query field (i.e. the field on the left of the SQL equal sign) on the index field operation, is directly caused by the driven table to do a full table scan.