0

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        |               |
+-----------------------+------------------+------------+---------------+
Improve this question
6
  • 1
    Search for indexes in mysql,use them.
    – Mihai
    Sep 3, 2014 at 8:15
  • Thanks for your comment. Indexes are indeed what led me to create this post as I was adding indexes and not seeing the performance gains I had hoped for. That is why I am looking to understand if this is a reasonable baseline (even without indexes) or if there is something else in play here. Thanks for any further comments
    – Brian FitzGerald
    Sep 3, 2014 at 13:50
  • you can run an EXPLAIN and EXPLAIN ANALYSE for query SELECT firstName FROM USER ask something more specific. If you don't understand the output then post it here.
    – Gufran
    Sep 3, 2014 at 19:34
  • Add an index on (filename) if you want to improve efficiency of the specific query. And why did you remove the other columns from the table description? Even one very wide varchar column, like the (800) you have, matters for the efficiency of a table scan. Also please note that the SHOW CREATE TABLE output is far more useful than the DESCRIBE output (we can see indexes, character sets, engine used, etc.)
    – ypercubeᵀᴹ
    Sep 4, 2014 at 14:15
  • Thank you! When you said (filename) did you mean firstName? I will add the columns back and display the table using the show create table syntax as requested. As to your question of why I removed them, I thought maybe it didn't matter and didn't want to expose so much of the tables structure if it wasn't necessary. I'll try adding the index tonight as you suggested.
    – Brian FitzGerald
    Sep 4, 2014 at 16:06

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.