There seems to be a lot of conjecture and lack of consensus about optimization, which makes sense since it's not a very well-defined thing. This one seemed to fill a lot of space with contatenating strings and " vs. ', which seems (judging from an earlier posting that had some nice benchmarks) like quite a waste of time.
On the other hand, I hadn't seen many msyql "optimization" suggestions, and I thought this one was interesting:
If you only want one line as a result from the database you should always use LIMIT 1. This way mysql stops searching when it finds the first line instead of continuing through the whole database, only to find that there weren't any more lines that matched the query.
When I use the devel module and look at my queries, I've got typically about 100 or 200 queries (not typical for drupal, this is the og - localization problem), of which many are pulling a single line. Has anyone thought about inserting something into drupal's database abstraction layer to see if the above would have any effect?
by definition, db_result is looking for a 1 row result (or the reult of an operation). I could imagine rewriting db_result so that it took the sql directly instead of the result set and did some regexp rewriting to insert limit = 1 when appropriate.
Posted by greggles on September 19, 2006 at 11:19pm
The thing is that we already spend too much time doing stuff in the database abstraction layer...http://buytaert.net/drupal-database-interaction regex is always slow so adding another regex feels like it might take us in the wrong direction as much as I like the idea in terms of simplicity.
I'll have to test the limit 1 idea and see if it makes a noticeable difference.
Comments
Saw this on Digg today,
Saw this on Digg today, seemed to be some good tips regarding PHP and MySQL optimization - http://www.dublish.com/articles/10.html
one useful bit?
There seems to be a lot of conjecture and lack of consensus about optimization, which makes sense since it's not a very well-defined thing. This one seemed to fill a lot of space with contatenating strings and " vs. ', which seems (judging from an earlier posting that had some nice benchmarks) like quite a waste of time.
On the other hand, I hadn't seen many msyql "optimization" suggestions, and I thought this one was interesting:
When I use the devel module and look at my queries, I've got typically about 100 or 200 queries (not typical for drupal, this is the og - localization problem), of which many are pulling a single line. Has anyone thought about inserting something into drupal's database abstraction layer to see if the above would have any effect?
merge db_result and db_query
by definition, db_result is looking for a 1 row result (or the reult of an operation). I could imagine rewriting db_result so that it took the sql directly instead of the result set and did some regexp rewriting to insert limit = 1 when appropriate.
well...
The thing is that we already spend too much time doing stuff in the database abstraction layer...http://buytaert.net/drupal-database-interaction regex is always slow so adding another regex feels like it might take us in the wrong direction as much as I like the idea in terms of simplicity.
I'll have to test the limit 1 idea and see if it makes a noticeable difference.
--
Growing Venture Solutions
Drupal Implementation and Support in Denver, CO
knaddison blog | Morris Animal Foundation
MySQL monitoring tools
Sorry, just noticed there was a specific group for DB issues. I just posted my comment here:
http://groups.drupal.org/node/1376