CODEWITHSUNDEEP
mysqlruby-on-railsdatabaseperformance
BananaNeil
BananaNeil

Reputation: 10762

RoR: Performance of Indexed Fields

I'm very confused at the moment, because I have two indexed columns on separate tables. One of the tables, 'users', has about 400,000 entries, and the other, 'posts', has about 8,000,000 entries.

I know these two fields are indexed, and i have confirmed it with my schema:

add_index "users", ["username"], :name => "index_users_on_username", :unique => true
add_index "posts", ["tag"], :name => "index_posts_on_tag", :unique => true

But somehow, when I run the following, it takes between 10 and 13 seconds:

User.find_by_username("mickeleh")

and when i run essentially the same thing on posts, it takes less than a second!

Post.find_by_tag("En-SKKB67Cg")

Can some one explain to me why this might be happening? And/or how I might be able to make my User.find_by_username method run faster?

Update:

I ran an explain on each of the calls and I got the following: 

mysql> explain SELECT `users`.* FROM `users` WHERE (lower(username) = 'mickeleh');
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 304548 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+

and

mysql> explain SELECT `posts`.* FROM `posts` WHERE `posts`.`tag` = 'En-SKKB67Cg' LIMIT 1;
+----+-------------+--------+-------+---------------------+---------------------+---------+-------+------+-------+
| id | select_type | table  | type  | possible_keys       | key                 | key_len | ref   | rows | Extra |
+----+-------------+--------+-------+---------------------+---------------------+---------+-------+------+-------+
|  1 | SIMPLE      | posts | const | index_posts_on_tag | index_posts_on_tag | 258     | const |    1 |       |
+----+-------------+--------+-------+---------------------+---------------------+---------+-------+------+-------+

I am not sure exactly how to read what was returned, so some help with be much appreciated.

I also created a new migration to 'reset' the indexes on the username column of users, as follows:

remove_index :users, :column => :username
add_index :users, :username, :unique => true

it did not work

I just realized one more thing that might be causing the issue.. The users table has a field that is a serialized Set.. and i dont think that would be causing the issue. but i figure it is possible.

FINAL UPDATE

So, for some reason, when i was a very novice RoR developer, i decided it would be a good idea to replace the 'find_by_username' method with my own that would make sure it searched for usernames ignoring the casing.

This was pretty ridiculous.. as i did not actually need to alter the original method to get the same response from differently cased queries.

So the moral of the story is do not include the following method in any model....

def self.find_by_username(name)
   User.where("lower(username) = '#{name.downcase}'")[0]
end

-face palm-

Upvotes: 2

Views: 99

Answers (2)

Frederick Cheung
Frederick Cheung

Reputation: 84114

I'm not sure where the call to lower() came from (is this a query from a uniqueness validation?) but that will prevent mysql from using an index, forcing it to do a full table scan, as the explain output shows.

Upvotes: 2

d.hoeffer
d.hoeffer

Reputation: 602

SELECT `users`.* FROM `users` WHERE (lower(username) = 'mickeleh');

This query is not using indexes. It can't. It will retrieve every single username, transform it to lowercase, and check if it's 'mickeleh'.

The solution would be to make sure stuff is in lowercase when it gets written into the table, and then you can get read of the lower() call and it will use the index.

I know where little about RoR,or why it would generate the queries that way, so I can't help you there.

Upvotes: 2

Related Questions