{SPAM 01.8} Re: [development] Caching, caching, caching...

[Kieran Lal]

On Jul 21, 2006, at 6:15 AM, Moshe Weitzman wrote:

> Dries Buytaert wrote:
>> On 14 Jul 2006, at 20:15, Dries Buytaert wrote:
>>>>> Maybe we should run the modified devel.module on drupal.org to  
>>>>> see what the most expensive queries are?
>>>>
>>>> SELECT * FROM url_alias, I'd be surprized if that had changed.  
>>>> The individual query is very short but there are probably 120  
>>>> per page on average.
>>>
>>> Are there reasonable assumptions we can make about certain URLs?   
>>> Like, can we assume that people don't want to alias '?q=qcomment/ 
>>> reply/123'?  Or ?q=contact?  Or q=user/register and ?q=user/ 
>>> password?  If we can make some simple assumptions, we might be  
>>> able to save a helluvalot database queries (per page view) ...   
>>> Food for thought.
>> Any thoughts here?
>
> i can't think of any reasonable assumptions. to me, the waste is  
> not that we have to perform so many queries, but rather that we do  
> so on every page view  even though the data rarely changes. to  
> address that, one should use mysql query cache or even memcached.
>
> mysqli provides another possibility: prepared statements. queries  
> that recur frequently benefit from prepared statements because  
> mysql does not need to construct a query plan for every query. see  
> http://dev.mysql.com/tech-resources/articles/4.1/prepared- 
> statements.html

I am not sure about mysqli but many db engines cache queries that are  
precompiled as well as their results.  This reduces parsing the query  
over and over again.   It's an optimization that can reduce some of  
the benefits of stored procedures.

>
> fyi, devel.module just got the ability to do query logging to the  
> DB. killes and i just need to quash one silly bug and it will be  
> ready.

Very nice!

Kieran