All geek questions in one place

A simple SQL statement takes longer to execute

Possible duplicate:
Drawbacks of quoting integers in a Mysql query?

I have a very simple Called Device table in the MYSql database.

+-----------------------------------+--------------+------+-----+----------------+ | Field | Type | Null | Key | Extra | +-----------------------------------+--------------+------+-----+----------------+ | DTYPE | varchar(31) | NO | | | | id | bigint(20) | NO | PRI | auto_increment | | dateCreated | datetime | NO | | | | dateModified | datetime | NO | | | | phoneNumber | varchar(255) | YES | MUL | | | version | bigint(20) | NO | | | | oldPhoneNumber | varchar(255) | YES | | | +-----------------------------------+--------------+------+-----+----------------+

This table contains over 100 thousand records. I execute a very simple request

 select * from AttDevice where phoneNumber = 5107357058;

This request takes almost 4-6 seconds, but when I change this request a bit, as shown below.

 select * from AttDevice where phoneNumber = '5107357058';

It takes almost no time to complete. Note that the phone number column is varchar. I do not understand why the first case takes more time, and later not. The difference between the two queries is a single quote. Can MYSQL handle these queries differently, if so, why?

EDIT 1

I used EXPLAIN and got the following output, but don’t know how to interpret these two results.

 mysql> EXPLAIN select * from AttDevice where phoneNumber = 5107357058; +----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | Device | ALL | phoneNumber,idx_Device_phoneNumber | NULL | NULL | NULL | 6482116 | Using where | +----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec) mysql> EXPLAIN select * from AttDevice where phoneNumber = '5107357058'; +----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+ | 1 | SIMPLE | Device | ref | phoneNumber,idx_Device_phoneNumber | phoneNumber | 258 | const | 2 | Using where | +----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)

Can someone explain to me the key, key_len and the lines present in the output of the EXPLAIN request?

+4
source share
5 answers

1) Thank you for the "EXPLAIN". We all (including you, I'm sure) knew that the problem was that mysql had to convert an integer to a string and had to do this for each row. But your EXPLAIN has proven it.

2) Here is a nice short article about EXPLAIN:

* Possible icons * shows which indexes are applied to this query, and the key says which one was actually used ... Finally, we wrote how many MySQL rows we had to search for rows to find the result set.

 Search value: key: type: ref: rows: ------------- --- ---- ---- ---- 5107357058 NULL ALL NULL 6482116 '5107357058' phoneNumber ref const 2

3) The column "ref" is "Columns compared to the index." In the second case, the string literal ("constant") "5107357058" was compared with the key "phone number". In the first case, there was no key to use (because your search term was a completely different type); therefore, "ref" was NULL.

4) Column "type" - "Connection type". “Link” means “All rows with corresponding index values ​​are read from this table” (in this case, 2 rows). "ALL" mans "full table scan". Which in this case means 6 million lines.

5) Here is the mysql documentation for "EXPLAIN":

+3
source

You tricked MySQL into making the wrong choice without quoting a phone number. Consider:

  • Column definition - varchar
  • In the first (unquoted) case, you specified the value as an integer (long). I would think that MySQL could understand this, but obviously did not, and did a full table scan.
  • In the second (indicated) case, you specified the search key in the correct data type (character), and MySQL chose the index over the full scan.
+2
source

The varchar index cannot be used when you use a number as an operand, an excerpt from the excellent documentation on implicit type conversions :

To compare a row column with a number, MySQL cannot use the index in the column to quickly find the value. If str_col is a column with an indexed row, the index cannot be used when performing a search in the following expression:

SELECT * FROM tbl_name WHERE str_col = 1;

The reason for this is that there are many different strings that can convert the value 1, for example, "1", "1" or "1a".

+2
source

I believe MySQL should convert the number to varchar in the first example. In the second example, this is not so. I guess where the difference happens.

+1
source

The first example scans the table one by one, the other uses an index.

http://dev.mysql.com/doc/refman/5.0/en/show-columns.html
If the key is MUL, several occurrences of the given value are allowed in the column. A column is the first column of a non-unit index, or a unique index that can contain NULL values.

Therefore, instead of scanning all zero values, the second query looks exclusively for non-zero values ​​that speed up the work.

.... I think.

+1
source

Source: https://habr.com/ru/post/1437700/

More articles:


All Articles