Speeding Up Your Selects and Sorts

By eric

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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.

Follow My Travels

Buy My Book

Archives

  • 2019
  • 2017
  • 2014
  • 2013
  • 2012
  • 2011
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006

New Posts By Email

writing