Set the data type in Mysql to optimize the instance of running speed _mysql

Today, I saw a very representative of the optimization case, here is a note to make a mark, to commemorate the casual field definition of the problem.

Recall that in the design of the table many people used to design the structure of the table into Varchar (), Varchar (255), and so on, although in most cases only 5-15 bytes. Let me take a look at the following case.
Query statement:

 SELECT Sql_no_cache Channel, COUNT (channel) as visitors from xxx_sources where client_id = 1301 GROUP by client_id, Channe L

The table (Client_id,channel) is a composite index.
Using explain, take a look at the execution plan, which is perfect for indexing.

Mysql> explain SELECT Sql_no_cache channel, COUNT (channel) as visitors from xxx_sources WHERE client_id = 1301 GROUP by CLIENT_ID, channel;
+----+-------------+-------------+-------+--------------------+--------------------+---------+------+---------- +--------------------------+
| id | select_type | table |  Type | possible_keys |  key    | key_len | ref | row S  | Extra     |
+----+-------------+-------------+-------+--------------------+--------------------+---------+------+---------- +--------------------------+
| 1 | Simple  | xxx_sources | index | idx_client_channel | idx_client_channel | 1032 | NULL | 20207319 | The Using where; Using Index |
+----+-------------+-------------+-------+--------------------+--------------------+---------+------+---------- +--------------------------+
1 row in Set (0.00 sec)

Take a look at the actual execution:

Mysql> SELECT Sql_no_cache Channel, COUNT (channel) as visitors from xxx_sources WHERE client_id = 1301 GROUP by Client_ ID, channel;
+---------+----------+
| channel | visitors
| +---------+----------+
| NULL |  0 |
+---------+----------+
1 row in Set (11.69 sec)

The actual implementation is very bad. The idea of communication, this execution from the index on the implementation of the plan is very perfect, it seems to have nothing to do with MySQL. Looking at the design of the table will find that client_id is also designed to
varchar (255). See here. You can try this by using the following methods:

Mysql> explain SELECT Sql_no_cache channel, COUNT (channel) as visitors from xxx_sources WHERE client_id = ' 1301 ' GROUP by client_id, channel;
+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+- -------------------------+
| id | select_type | table  | type | possible_keys |  key    | key_len | ref | rows | Extra     |
+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+- -------------------------+
| 1 | Simple  | xxx_sources | ref | idx_client_channel | idx_client_channel | 258  | const | 457184 | The Using where; Using Index |
+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+- -------------------------+
1 row in Set (0.00 sec)

In terms of the implementation plan, it's almost, but it's a lot worse. Key_len from 1032 to 258, the execution plan becomes const based on the lookup of equals, the number of rows from the original tens to the 100,000 level. You can understand IO.
Save a lot.
And then the actual implementation:

Mysql> SELECT Sql_no_cache Channel, COUNT (channel) as visitors from xxx_sources WHERE client_id = ' 1301 ' GROUP by Clien T_ID, channel;
+---------+----------+
| channel | visitors
| +---------+----------+
| NULL |  0 |
+---------+----------+
1 row in Set (0.25 sec)

Wow, from 11.69 seconds into 0.25 seconds, what is this concept, optimized how many times, calculate it.

See what's going on here, remember this case, well, yes, and then you can add quotes to optimize. So why not ask, can be optimized, why this?
Let's take a look at the first question:
Can it be optimized?
The answer is certainly OK. From the length of the index to see 258 or a very large data, for client_id This field from the name, also only stored data type value, then why not use an int unsigned to save it,
The length of the index will immediately drop from 258 to 4. Doesn't that save a lot of money?
Next look at the second question, why is this?
The reason has two points, also based on a principle, based on the cost optimizer. For client_id defined as a character-type value when the table is defined, a numeric value is passed in the query, which takes a numeric conversion, the beginning of the tragedy, and finally
Causes MySQL to select a completed index to scan.

What do we need to be aware of in this case?
Reasonable choice of data type, the basic work is too important, it is called to win at the starting line, everything can not be casually, do not define a table as a drop of the main building outside all the other varchar (255). Be careful when indexing a database double/float this field.