Add some indexes to the {metatags} table so the common API functions run as fast as they possibly could.

Comments

juampynr’s picture

The metatag table already has indexes. So there is little extra which can be done to improve a query with a long list of nodes to be loaded, such as the one mentioned at #2139925: Very slow query (using IN operator).

juampynr’s picture

Correction, there is just one primary index for the composite key. Me and @q0rban played with indexes to see if we could optimize the query at metatag_metatags_load_multiple(), until we realized that beta9 already did it by removing revision_ids from the WHERE clause.

I do not know which API functions would benefit from extra indexes at the moment.

ttkaminski’s picture

I found that adding an index for (entity_type,revision_id) sped up some of the metatag queries significantly. In particular, this query was slow:

SELECT m.entity_id AS entity_id, m.revision_id AS revision_id, m.language AS language, m.data AS data 
FROM metatag m 
WHERE (m.entity_type = 'node') AND (m.revision_id IN (1,2,3,4)) 
ORDER BY entity_id ASC, revision_id ASC;

From the EXPLAIN statement, it showed that in my case, 7500 rows were being scanned. After adding the index, only 4 rows were scanned.

damienmckenna’s picture

@ttkaminski: If you'd mind rolling a patch for this I'd be happy to include it in 1.0 this week!

ttkaminski’s picture

Status: Active » Needs review
StatusFileSize
new735 bytes

Attached is a patch.

damienmckenna’s picture

damienmckenna’s picture

StatusFileSize
new858 bytes

A slight update to add a drupal_set_message call, and fix two errant spaces.

damienmckenna’s picture

Status: Needs review » Fixed

Committed. Thanks @ttkaminski!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.