Posted by j0k3z on August 15, 2010 at 1:54am
On my Drupal site my users set a location and then they can browse node results that are sorted based on distance from them.
These queries are pretty big and are taking 6000ms or more each time they are run. I am using caching so if they hit that same page of results again the query is cached, but as soon as the use the pager to navigate to the next 15 its technically a new query so it must hit the DB again.
Sorting by location seems to be extremely intensive, but its the basis of my website and cannot be removed.
What can I do to get better performance?
Comments
Index the sort field
The problem is that your database is having to manually sort (table sort) every possible result for every query prior to displaying a result set (for any page). The best way around this is usually to add an index to the sort field(s) in the appropriate table in your database. This can be done using phpmyadmin, MySQL Query Browser / Workbench, or any other db admin tool you may have installed. When the sort field(s) are indexed, the database keeps running track of their order, and can thus sort much more efficiently.
It is possible that the sort is not your only issue -- so I would recommend running EXPLAIN on your query in one of the tools mentioned above to figure out where the slow step(s) are and add field indexes accordingly. If you do a search for "mysql optimization explain" -- you should get some relevant guidance on how to interpret and respond to the results returned back from EXPLAIN.
If this list is created with
If this list is created with Views, you can use DB Tuner module to automatically add appropriate indexes.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Can it still index the data
Can it still index the data if every single user is sorting it in a different order based on their location?
Here is the query that views generates - http://pastebin.com/dNTrPM0f
Also, on this views page I also have the same view again except it is formatted as a map next to the same results as a table using attachments -- so its forced to run this slow query twice just to display the results in a different way.
Explain
I see a few things there, node_access is one of them.
To be sure, on your server, execute an EXPLAIN for this query, and add it to pastebin.
Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.
Here is the pastebin of the
Here is the pastebin of the EXPLAIN - http://pastebin.com/yrJMvP48 Its kind of mangled so I also took a screenshot - http://img836.imageshack.us/img836/7299/explainu.png
I should also mention I am on Amazon EC2 so in addition to optimizing the query if possible I may have some other work to do on the server itself. I ran the same query on a $6 a month shared Hostgator server it was only taking 60ms to execute the same query (although there are less nodes (1800) on that version of the site)
Too many joins ...
I mistook domain access with node access in my previous comment.
But the issue here is that there are way too many joins in this query. It also has filesort and temporary table, so no matter what it will be slow.
They key for flag_content is long, and the table is joined twice. The table flag_content_node is also joined twice.
The 3rd, 5th, and 8th line in the EXPLAIN have no index associated, so that is an area to explore more and see if you can add indexes.
Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.
Check the following link for
Check the following link for some tips on interpreting the output for your EXPLAIN:
http://dev.mysql.com/doc/refman/5.0/en/explain-output.htm
This query looks a lot like something that was originally a views query and kind of took on a life of its own. Any line listed as 'ALL' or 'Index' is going to be a problem area - it means the database is having to scan every record in those tables. Usually this is due to either missing indexes or a poorly constructed join.
To assess the performance impact of a query, you can multiply down on the 'rows' column. In the case of your query, the db is having to process 6109 * 1437 * 1437 rows. That is quite a bit of overhead. Also, I would try to avoid joining ON more than one field pair wherever possible -- I find this usually causes performance issues. Instead, try moving the filtering clauses that aren't directly related to the join down into your WHERE clause to ensure the query optimizer is best utilizing our indexes during joins.
DBTuner
Let me know if the dbtuner module missed any indexes. If it did I would like to investigate it and see how I could get it to detect it in the future.
The saturdaydrink CCK type, if that is a text cck field I recommend setting the max length to 255 (if possible) and adding an index on it. Use the latest dev of dbtuner and it should spell out exactly what to do; after fixing the cck field add an index on it. In short read the messages that dbtuner throws out on the index page, it will help a lot if you set the max length of text fields (because then you can index it).
This is a views query so I
This is a views query so I cant exactly change it unless I edit my view - but then obviously I wont be filtering the correct content.
I am going to look into db tuner. Im thinking a lot of this must be server side issue though because as I mentioned above, the same site on a crappy shared server is giving me much better performance on the same intensive queries.