The following issue has been SUBMITTED. ====================================================================== http://www.dbmail.org/mantis/view.php?id=1009 ====================================================================== Reported By: rmoesbergen Assigned To: ====================================================================== Project: DBMail Issue ID: 1009 Category: Database layer Reproducibility: always Severity: tweak Priority: normal Status: new target: ====================================================================== Date Submitted: 31-May-13 10:26 CEST Last Modified: 31-May-13 10:26 CEST ====================================================================== Summary: Slow query used for user lookup, MySQL Description: In the db_user_exists function in dm_db.c, a select statement is used that is not using any index and therefore does a full table scan:
s = db_stmt_prepare(c, "SELECT user_idnr FROM %susers WHERE lower(userid) = lower(?)", DBPFX); The problem is the use of lower() around the userid field in the WHERE clause. MySQL cannot use an index when conversions are done on a field in a comparison. The statement is logged in the 'slow query log' for every invocation when log-queries-not-using-indexes is enabled in my.cnf. Solution (at least for mysql) is to remove the lower() in the query, it's useless anyway since an non-case-sensitive collation is used by default... However: This might break on other databases that might not use a case-insensitive collation. If that's the case, the fix would be to always insert lower-case usernames into the database (in the dbmail-util command), so lower-case conversion is never needed. ====================================================================== Issue History Date Modified Username Field Change ====================================================================== 31-May-13 10:26 rmoesbergen New Issue ====================================================================== _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
