I have a problem with a Drupal 6 site working with many nodes.

This is the SQL given by Views: https://gist.github.com/ranbogmord/79bfa50e40db1322c9e0

The query takes 5-10s to run through and the specific content-type has 30 fields bound to itself. 21 of those are exposed.

Imagine this as orders in a business system.


  • Is your MySQL instance optimized? – mpdonadio Jul 1 '14 at 18:44
  • @MPD fine tuning MySQL to his webapp is possible. Honestly I think the next lowest hanging fruit is a simple EXPLAIN diagnostic of what the query is doing. Drupal is built to be good for 80%+ of use cases. I find I need to add additional indexes to tables when i start building my own queries or using EFQ. His Views search is no different -- it's a query builder; not a smart query builder. – tenken Jul 1 '14 at 19:35

Try denormalising the database by storing the relevant node objects in a single caching table. You can model your database schema on that of the core caching tables. That should speed up the query by reducing the amount of joins. Also, put indexes on any columns used in where clauses. If you insert/update rows in your caching table on update/insert of nodes using hook_nodeapi, your caching table should remain fresh. You could then either replace the view with a db_query in a custom module, or you could expose your table to views.

I haven't tried it, but you may be able to achieve this using the D6 object cache module.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.