Mysql – is this dead simple query too slow


I have added and removed a few indexes from my user table and, while testing, didn't see the performance gains I had hoped. As a result, I removed the indexes and tried a simple query to see what my baseline speed is.

With so few records in the table, it seems like this "baseline speed" should be way faster, but I'm not experienced in MySQL performance tuning, so I'm not sure.

My user table (InnoDb) has 4344 records.

If I run select firstName from user; the query takes roughly 0.5 seconds to complete.

Thanks in advance for any insight:

|         Field         |       Type       | Allow Null | Default Value |
| userId                | int(10) UNSIGNED | No         |               |
| email                 | varchar(150)     | No         |               |
| password              | varchar(50)      | Yes        |               |
| passwordSalt          | varchar(50)      | Yes        |               |
| firstName             | varchar(50)      | Yes        |               |
| lastName              | varchar(50)      | Yes        |               |
| country               | varchar(50)      | Yes        |               |
| phone1                | varchar(10)      | Yes        |               |
| phone2                | varchar(10)      | Yes        |               |

Best Answer

The statement you are trying to tune ("select firstName from user;") doesn't have a where clause so it will perform a full table scan. When firstName is indexed, then a scan of the index will occur. This will be faster on the basis that the amount of data the engine must read and is substantially less than the entire row.

Most applications are interested in smaller sets of data (i.e., what are the users with a first name of Peter) and not all of the stored data values. This is where tuning really pays off. A firstName index would be beneficial to find people with a certain firstName but comes at a cost - inserting, updating and deleting row becomes slower. You do not want to add indexes indiscriminately.