Speeding Up Your Selects and Sorts

When you are using a framework, they typically set your VARCHAR size automatically to 255. This is normally fine since you are letting the framework abstract you away from most of the SQL. But if you interact with your SQL, there is a way to get a decent speed increase on your SELECTs and ORDER BYs when you are working with VARCHARs.

The VARCHAR data type is only variable character size for storage, not for sorting and buffering. In fact, since the MySQL optimizer doesn’t know how big the data in that column can be, it has to allocate the maximum size possible for that column. So for sorting and buffering of the name and email columns below would take up 310 bytes per row.

To fix that you should alter the size of your columns. Imagine having the optimizer have to go through an additional 310 bytes for every row. If there are 500k rows in the table time 310 bytes for each row, that can add up in the amount of memory that the optimizer has to use to perform the sorting/buffering.

Consider the following table of businesses:

mysql> DESC businesses;
+----------------+--------------+------+-----+---------+----------------+
| FIELD          | TYPE         | NULL | KEY | DEFAULT | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | INT(11)      | NO   | PRI | NULL    | AUTO_INCREMENT |
| name           | VARCHAR(255) | YES  |     | NULL    |                |
| url            | VARCHAR(255) | YES  |     | NULL    |                |
| email          | VARCHAR(255) | YES  |     | NULL    |                |
| description    | text         | YES  |     | NULL    |                |
| created_at     | datetime     | YES  |     | NULL    |                |
| updated_at     | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
7 ROWS IN SET (0.00 sec)

mysql> SELECT MAX(LENGTH(name)), MAX(LENGTH(email)), MAX(LENGTH(url)) FROM businesses;
+-------------------+--------------------+------------------+
| MAX(LENGTH(name)) | MAX(LENGTH(email)) | MAX(LENGTH(url)) |
+-------------------+--------------------+------------------+
|                53 |                 36 |               40 |
+-------------------+--------------------+------------------+
1 ROW IN SET (0.40 sec)

You can change the name to column sizes to 75 (name), 50 (email), and 100 (url). But realistically you don’t want to chance things getting cut off, so it may be better off to settle on each column here being a VARCHAR(100). Even that would save drastically on the space required to perform a sort and buffer the results.

  • wagnerbianchi

    The speed of Varchar or Char on MySQL depends on the Storage Engine that you are working with. When using InnoDB to controll its database tables, prefer to user VARCHAR, InnoDB and VARCHAR works fine. Well, when you are working with MyISAM, choose to work with CHAR on all columns because in this time, the tables will work with fixed rows and this is better and more performantics.

    Best regards.

  • http://eric.lubow.org Eric Lubow

    I understand why you're saying that for MyISAM you should use CHAR storage but think about the implications on a more macro scale if you are using MyISAM. If you have 50 tables with 5 CHAR(255)s per table and 50k rows per table, that's 3.18G as opposed to the space saving nature of a VARCHAR which also allows for more flexibility in your application. However, I was referring to the optimizer and how it loads the data into memory and sorts it. By minimizing the VARCHAR columns in a row, sorting and buffering will be faster in general.

  • Pingback: Speeding Up Your Selects and Sorts | BeginnerPC : The Best Online Tutorials : Linux , Windows , Apple , iPhone