[development] Database queries on drupal.org

[Kieran Lal]

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/
>
>