On Jul 26, 2006, at 11:03 PM, Dries Buytaert wrote:
On 26 Jul 2006, at 18:10, Kieran Lal wrote:
Great! If I can get a copy of the Drupal.org schema then I'll write the EXPLAINs for both these queries and the Slow queries from the Drupal.org databases. We just need a PHP Snippet that puts an EXPLAIN in front of these queries and converts INSERTS, UPDATES, and DELETES to SELECTS to use EXPLAINs.
I'll then ask for the snippet to be run on Drupal.org by a maintainer with PHP Input format privileges so I can review the EXPLAINS. It's also worth noting that Peter Zaitsev, the MySQL performance lead, has been explaining how MySQL EXPLAIN can be wrong here: http://www.mysqlperformanceblog.com/2006/07/24/mysql- explain-limits-and-errors/
Isn't our time better spend building that functionality into the devel.module (along with a permission)? It would be convenient if there was an 'explain' link on the query overview page.
Unfortunately, no. mysqldump -d drupal_org_db is pretty fast and I can quickly write the explains through trial and error. We submitted a patch a few months ago to the devel module to allow the slow query log from MySQL to be displayed in the devel.module interface. It was decided at the time that configuring slow query logs was too difficult and not worth the effort to expose a log through the devel.module. So for starters we don't have the slowest queries to work on. Next, even if we did have the slow queries in devel.module converting queries into a format that works with Explain is not straight forward. The query dumped by the slow query log needs to be modified to work in explain. Sometimes it's variable replacement, some times it's clearing the syntax for EXPLAIN to work. Next we need to convert UPDATE, INSERT, DELETE to SELECTs. So I agree, building tools is useful for everyone, but quickly showing how we use the existing tools effectively is more useful. I suspect that you'll see the SLOW QUERY log accessible through queries in MySQL rather than a log file in the near future. Cheers, Kieran
-- Dries Buytaert :: http://www.buytaert.net/