Drupal adds LOWER() to column names when doing a SELECT
I have been playing a lot with Drupal lately. I must say it does feel weird getting back to PHP after so much Ruby; but it is a refreshing change. One of the the things I noticed is that Drupal 6 adds a LOWER() to the column names when it does a select. Not that this is bad but then it confuses MySQL, and instead of using the "name" index it runs through the entire database. This is not a issue if your site has a few hundred users. But now consider a site that has about 5 million users (which is what I am dealing with)
I just finished hacking up this patch, that prevents it from adding the LOWER() to the column names. This boosted something as simple as the login process from 5 seconds to milliseconds. 5 seconds may not sound a lot to you; but then I use a Dell 1950 as my development box. Not that it is the best box in the world, but it is pretty powerful.
diff --git a/modules/user/user.module b/modules/user/user.module index 5ada13d..922e8ec 100644 --- a/modules/user/user.module +++ b/modules/user/user.module @@ -160,7 +160,7 @@ function user_load($array = array()) { $params[] = md5($value); } else { - $query[]= "LOWER($key) = LOWER('%s')"; + $query[]= "$key = LOWER('%s')"; $params[] = $value; } } @@ -574,13 +574,13 @@ function user_search($op = 'search', $keys = NULL, $skip_access_check = FALSE) { $keys = preg_replace('!\*+!', '%', $keys); if (user_access('administer users')) { // Administrators can also search in the otherwise private email field. - $result = pager_query("SELECT name, uid, mail FROM {users} WHERE LOWER(name) LIKE LOWER('%%%s%%') OR LOWER(mail) LIKE LOWER('%%%s%%')", 15, 0, NULL, $keys, $keys); + $result = pager_query("SELECT name, uid, mail FROM {users} WHERE name LIKE LOWER('%%%s%%') OR mail LIKE LOWER('%%%s%%')", 15, 0, NULL, $keys, $keys); while ($account = db_fetch_object($result)) { $find[] = array('title' => $account->name .' ('. $account->mail .')', 'link' => url('user/'. $account->uid, array('absolute' => TRUE))); } } else { - $result = pager_query("SELECT name, uid FROM {users} WHERE LOWER(name) LIKE LOWER('%%%s%%')", 15, 0, NULL, $keys); + $result = pager_query("SELECT name, uid FROM {users} WHERE name LIKE LOWER('%%%s%%')", 15, 0, NULL, $keys); while ($account = db_fetch_object($result)) { $find[] = array('title' => $account->name, 'link' => url('user/'. $account->uid, array('absolute' => TRUE))); } @@ -1549,7 +1549,7 @@ function _user_edit_validate($uid, &$edit) { if ($error = user_validate_name($edit['name'])) { form_set_error('name', $error); } - else if (db_result(db_query("SELECT COUNT(*) FROM {users} WHERE uid != %d AND LOWER(name) = LOWER('%s')", $uid, $edit['name'])) > 0) { + else if (db_result(db_query("SELECT COUNT(*) FROM {users} WHERE uid != %d AND name = LOWER('%s')", $uid, $edit['name'])) > 0) { form_set_error('name', t('The name %name is already taken.', array('%name' => $edit['name']))); } else if (drupal_is_denied('user', $edit['name'])) { @@ -1561,7 +1561,7 @@ function _user_edit_validate($uid, &$edit) { if ($error = user_validate_mail($edit['mail'])) { form_set_error('mail', $error); } - else if (db_result(db_query("SELECT COUNT(*) FROM {users} WHERE uid != %d AND LOWER(mail) = LOWER('%s')", $uid, $edit['mail'])) > 0) { + else if (db_result(db_query("SELECT COUNT(*) FROM {users} WHERE uid != %d AND mail = LOWER('%s')", $uid, $edit['mail'])) > 0) { form_set_error('mail', t('The e-mail address %email is already registered. <a href="@password">Have you forgotten your password?</a>', array('%email' => $edit['mail'], '@password' => url('user/password')))); } else if (drupal_is_denied('mail', $edit['mail'])) {
Flickr recent photos
1 comment
I want to quote your post in
I want to quote your post in my blog. It can?
And you et an account on Twitter?
Post new comment