[development] Drupal could be 10x times faster with correct indexing

[Khalid Baheyeldin]

> We did do an EXPLAIN ANALYZE on every query in our installation (by
> exposing each one with devel) and this included a large number of
> contributed modules. The most problematic queries were the ones coming from
> user_points and views, which should come as no surprise,


A patch by a large site was commited. It adds a few indexes that increase
the performance for the txn table.

The most significant problem we faced, outside of straight up bad queries,
> was repetitive queries. The userpoints module was challenging to work with
> and we did need to rewrite most of the data access there to make it suitable
> for a production environment. There are other modules we changed, but this
> one required the most effort. We had some pages that were hitting the points
> tables over 100 times per page load, even for anonymous users with memcache
> installed.
>

If this is a list of users with points for each listed beside their names,
then yes, it has to query the table several times on the page. Just like
user_load or node_load for different users/nodes.

If you have implemented some sort of smart caching, then we are looking
forward to your patches in the issue queue.


> What's ironic about this discussion is the PostGres developer base is
> growing at a faster rate than MySQL and adoption is increasing for a number
> of other FOSS projects.


What I have seen is that Sun's purchase of MySQL, and MySQL's management
revoking the open source aspect of some features, then reversing that
decision, all this has not gone well with some.

Moreover, the departure from MySQL/Sun of Jim Starkey, architect of the new
transactional Falcon engine, and Oracle's ownership of InnoBase has left the
question of the future of a transactional engine (Maria is not
transactional, just a replacement for MyISAM). PostgreSQL has been
transactional from the start though.
-- 
Khalid M. Baheyeldin
2bits.com, Inc.
http://2bits.com
Drupal optimization, development, customization and consulting.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20080709/439e13d0/attachment.htm