Military rule applications developed by MySQL database (1) _ MySQL

Military rule application developed by MySQL database (I) bitsCN.com

Slides address: http://www.slideshare.net/mysqlops/mysql-9838563
Comparison of column values of the same data type
Principle: number-to-number, character-to-character
Comparison between value columns and character types
• Conversion to precision at the same time
• Comparison
Character column and numeric type comparison
• Entire character column to numeric value
• Index query is not used
The instance displayed on page 43 of slides is a comparison between character columns and numeric values. compared with the same type, the former consumes about 28 times the latter.
The test results are as follows:
Mysql> select sum (capital) FROM lend_inoutdetail WHERE rec_type = '1' AND user_id =
319;
+ -------------- +
| SUM (capital) |
+ -------------- +
| 1, 11942.5300 |
+ -------------- +
1 row in set (1.08 sec)

Mysql> select sum (capital) FROM lend_inoutdetail WHERE rec_type = 1 AND user_id = 31
9;
+ -------------- +
| SUM (capital) |
+ -------------- +
| 1, 11942.5300 |
+ -------------- +
1 row in set (5.30 sec)

Mysql>
The time difference is about 4 times. The data volume in my database is relatively small. the larger the data volume, the larger the time difference.
I looked back at the project code and found that many character columns are compared with numbers. I need to correct the code and keep this rule in mind in future code.

Excerpt

BitsCN.com