Mysql – is this dead simple query too slow

innodbMySQLperformance

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.

Related Solutions

Mysql – How to speed up this slow simple query

Try adding two indexes:

ALTER TABLE game
  ADD INDEX deleted_sportid_hometeam_date_IX          -- choose names
    (deleted, sportid, hometeam, date), 
  ADD INDEX deleted_sportid_awayteam_date_IX          -- for the indexes
    (deleted, sportid, awayteam, date)  ;

and then running this version:

SELECT * FROM game 
WHERE hometeam = 29
  AND sportid = 1 
  AND date BETWEEN '2012-07-01' AND '2013-06-30'
  AND deleted = 0

UNION ALL

SELECT * FROM game
WHERE awayteam = 29 
  AND sportid = 1 
  AND date BETWEEN '2012-07-01' AND '2013-06-30'
  AND deleted = 0 ;

You can still try your original query after having added the 2 indexes and the efficiency will probably be improved (but not much, depends on the selectivity of the deleted - sportid combination.) But I don't think the optimizer is smart enough to understand that this (rewriting) is equivalent to that (original query) and that it could use both indexes, one for the 1st and the other for the 2nd part, so you will only have a small efficiency gain.

So, the "why is it faster this way" has mainly to do with the OR condition and with the limited ability of the optimizer to rewrite queries in equivalent forms. This rewriting for example would never be produced anyway by an automated optimizer (because I assumed that hometeam and awayteam are never the same, which the optimizer cannot guess.) If home team and away team could be the same, then we'd have to use UNION (not UNION ALL).

B-tree indexes are super-good for conditions with AND, only. When there are ORs or otherwise complicated conditions, they are not so good. Occasionally - like in this case - there is a way to rewrite a query so it has only ANDs. The last part of the execution (the UNION ALL) can be executed in MySQL by running the two subqueries one after the other and showing all results from both so there is no calculations or delay there.

Mysql – Why is this query slow, it seems simple

The query is slow because first, this query has an OR condition which cannot be optimized with a single index (while conditions with only AND can) and second, because besides the emails, it has to check the isActive=1 condition. So it prefers to do a table scan. The other possible path would be to use both email indexes with the Index Merge Union Access Algorithm but for some reason the optimizer does not choose this plan.

One solution would be to alter the two indexes you have, appending the isActive column at the end:

ALTER TABLE user 
  DROP INDEX the_name_of_the existing_index,
  DROP INDEX the_name_of_the_other_index,
  ADD INDEX emailAddress_isActive_IX
    (emailAddress, isActive),
  ADD INDEX emailAddress2_isActive_IX
    (emailAddress2, isActive) ;

If it still slow (and/or does not do an index merge but prefers the table scan), you could try to rewrite the query with UNION:

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND u.emailAddress = 'user@domain.com'

UNION 

SELECT u.userId
FROM user AS u
WHERE u.isActive=1
AND u.emailAddress2 = 'user@domain.com' ;

Another option - without necessarily adding/altering indexes - is to nest the query into 2 layers, keeping only the email conditions in the internal layer, essentially forcing MySQL to consider using index-merge with the two available indexes:

SELECT userId 
FROM 
    ( SELECT userId, isActive 
      FROM user AS u 
      WHERE emailAddress = 'user@domain.com' 
         OR emailAddress2 = 'user@domain.com'
    ) x 
WHERE isActive=1 ;

Best Answer

Related Solutions

Mysql – How to speed up this slow simple query

Mysql – Why is this query slow, it seems simple

Related Question