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

[Jean-Michel Pouré]

Dear friends,

Today my Drupal 6.2 server was flooded and I discovered that there was a
real lack of indexing in Drupal.

Here is the issue with solutions:
http://drupal.org/node/276742

When using MySQL, you don't really know how SQL queries are treated,
except execution times that may be monitored (?).

On the converse, PostgreSQL offers full logging of queries and access to
query plans, showing how the database executes data.

In the above issue, I could speed-up a simple query by 10 times. You may
not notice this issue when using small Drupal sites (because the whole
database executes in shared memory) or when using Drupal with caching.
But ... on large installations like mine, you cannot cache everything
without a huge amount of memory. And the result is SLOW.

Using PostgreSQL, normal queries should run in less than one
millisecond, usually 0,2 millisecond. Long queries with difficult JOINS
should run in less that 5 milliseconds. This way, the database server is
always faster than anay kind of PHP cache. And you can use large sites
on small sites.

There is an extensive query debugging needed on Drupal:
1) Turn on extensive logging in PostgreSQL, including query execution
times.
2) Whenever a query executes in more than 1 millisecond, run psql or
pgAdmin (graphical client) to run EXPLAIN ANALYSE.
3) Find sequential scans and fix them. Most of times, JOINS and clauses
and ORDER BY should be on indexes.
4) Add proper indexing
5) Run EXPLAIN ANALYSE again. Times usualy are divided by 10.
6) Add indexes everywhere.

After this hard work, a normal Drupal 6.2 installation should be able to
answer thousands of queries.

Do not hesitate to answer my issue :
http://drupal.org/node/276742

The issue offers a pratical example.

Again, this kind of problem will only show on large sites (like
Drupal.org), not a small installation. But I consider this as a bug, a
very important issue which should be fixed ASAP.

Kind regards,
Jean-Michel Pouré