[development] Database queries on drupal.org
kieran at civicspacelabs.org
Thu Jul 27 16:06:14 UTC 2006
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-
> 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.
> Dries Buytaert :: http://www.buytaert.net/
More information about the development