0

I'm looking to move the following code away from ActiveRecord to pure SQL for a performance increase. What would be the best way to write this query in pure SQL (MySQL DB)?

User.count(:conditions => ["email = ?",params[:email]]) > 0

Thanks

Splashlin
  • 7,225
  • 12
  • 46
  • 50

1 Answers1

3

Analogously to find_by_sql you can use count_by_sql:

User.count_by_sql(["SELECT COUNT(*) FROM users u WHERE u.email = ?", params[:email]]) > 0

Remember also to use the syntax ["... ? ...", var] here to protect against SQL injection.

However, I doubt that you can achieve a significant performance improvement by that. Test it. If it's not faster, stay with the ActiveRecord version or try to find a more nifty solution to your problem.

Edit:

If you just want to test whether an given email is already contained in the table you could also test the performance of User.find_by_email(params[:email]).present?

Community
  • 1
  • 1
crispy
  • 5,737
  • 4
  • 33
  • 45
  • 1
    +1 - @Spashlin - poor performance probably means your User.Email column needs an index. – StuartLC Aug 29 '10 at 15:21
  • 1
    Alternately, you could use `User.exists?(:email => params[:email])` if you're just looking to see if the user is already present. This takes care of escaping automatically. – Peter Brown Aug 29 '10 at 16:18