Posted by locomo on July 18, 2010 at 7:10pm
Hi,
Hopefully this is an appropriate question for this group. Any ideas why this type of query would show up repeatedly in my slow query log? Is there something I can do to optimize this?
thanks.
# Time: 100715 12:07:03
# User@Host: drupal_db[drupal_db] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 12
SELECT 1 FROM access WHERE type = 'host' AND LOWER('67.218.116.166') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1;
# Time: 100715 12:08:17
# User@Host: drupal_db[drupal_db] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 12
SELECT 1 FROM access WHERE type = 'host' AND LOWER('216.129.119.47') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1;
# Time: 100715 12:08:50
# User@Host: drupal_db[drupal_db] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 12
SELECT 1 FROM access WHERE type = 'host' AND LOWER('67.218.116.165') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1;
# Time: 100715 12:09:30
# User@Host: drupal_db[drupal_db] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 12
SELECT 1 FROM access WHERE type = 'host' AND LOWER('216.129.119.49') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1;
# Time: 100715 12:10:47
# User@Host: drupal_db[drupal_db] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 12
SELECT 1 FROM access WHERE type = 'host' AND LOWER('216.129.119.43') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1;Categories: access, slow queries
Comments
This would appear in the slow
This would appear in the slow log if you have a large access table as the LOWER() statements can't be indexed. If you don't use forum and don't show the comment new markers you could dump the contents of the table but you should really understand what you are doing there before you take that step. Alternatively you could switch to Pressflow which doesn't do those queries, it prefers instead to do queries without the LOWER() which means you could add indexes if needed
Full Fat Things ( http://fullfatthings.com ), my Drupal consultancy that makes sites fast.
Thanks .. that definitely
Thanks .. that definitely makes sense. The thing is that I only have 12 records in my access table.
we're talking about 'access' right? not 'accesslog' ?
thank you.
access is missing indexes
Get the dbtuner module
Indexes
Experimental Indexes for Core modules - Unproven indexes; may hurt or help performance
Check all 3 for the access table
great - thanks.. this module
great - thanks.. this module looks excellent
i'll try this out and report back
thanks mikeytown2 .. so far
thanks mikeytown2 .. so far so good, not seeing these anymore in my slow query log
great module. more db
great module. more db optimization would be great.
I couldn't get this one out
I couldn't get this one out my my slow query log, and I don't even use the access table. I had previously used the following to eradicate the query (although it looks like this doesn't quite apply to the latest Drupal core any more, but you get the idea).
Index: includes/bootstrap.inc===================================================================
--- includes/bootstrap.inc (revision 1215)
+++ includes/bootstrap.inc (working copy)
@@ -935,6 +935,11 @@
* TRUE if access is denied, FALSE if access is allowed.
*/
function drupal_is_denied($type, $mask) {
+ // We don't use access checking, and this is a major cause of slow MySQL Queries
+ // Drupal 7 makes this an optional feature, but it hasn't been backported to Drupal 6
+ // So we just hack it out ourselves.
+ return FALSE;
+
// Because this function is called for every page request, both cached
// and non-cached pages, we tried to optimize it as much as possible.
// We deny access if the only matching records in the {access} table have
NOTE: I would never normally promote a core hack - but if you are using this core on one specific site, and you really have no need for this feature, I don't see any problem with this - as long as make note of the fact that you have patched the core, and keep the patch handy! ;)
--
Tom
www.systemseed.com - drupal development. drupal training. drupal support.
Pressflow
This is one of the optimizations that pressflow does. So using pressflow with the dbtuner index on the access table should give you better performance.
http://api.drupal.org/api/function/drupal_is_denied/6
CORE
<?phpfunction drupal_is_denied($type, $mask) {
$sql = "SELECT 1 FROM {access} WHERE type = '%s' AND LOWER('%s') LIKE LOWER(mask) AND status = %d";
return db_result(db_query_range($sql, $type, $mask, 0, 0, 1)) && !db_result(db_query_range($sql, $type, $mask, 1, 0, 1));
}
?>
PRESSFLOW
<?phpfunction drupal_is_denied($type, $mask) {
$sql = "SELECT 1 FROM {access} WHERE type = '%s' AND '%s' LIKE mask AND status = %d";
return db_result(db_query_range($sql, $type, $mask, 0, 0, 1)) && !db_result(db_query_range($sql, $type, $mask, 1, 0, 1));
}
?>
Because the lower function is not used, mysql can use an index. In D7 there is a variable so you can disable this check in settings.php
http://api.drupal.org/api/function/drupal_is_denied/7
More info about lower in core
http://drupal.org/node/83738
Query is slow even when access table is empty
# Query_time: 7 Lock_time: 0 Rows_sent: 0 Rows_examined: 0SELECT 1 FROM access WHERE type = 'host' AND '208.122.31.19' LIKE mask AND status = 0 LIMIT 0, 1;
This was frequently seen in mysql slow query log even though access table was empty. The site used pressflow. I added an index on type, mask, status and then I stopped seeing this query in the slow query log.
So would an index speed up a query even if the table is empty?
Check out our Free Drupal site review