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        |               |
+-----------------------+------------------+------------+---------------+
  • 1
    Search for indexes in mysql,use them. – Mihai Sep 3 '14 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 '14 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 '14 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 '14 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 '14 at 16:06
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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