The {locales_source} has an index of (source(30), context) where source is a text column and context a varchar.

We should definitely not do that:

  • only MySQL support index prefixes. on databases that don't support this, the index will be impossible to create or will perform very badly
  • most of the rows have the same value for context is bounded, so it should be first so as to match early

At the minimum, we should invert source and context, and use (context, source(30)). We could also consider being a bit smarter in how we query this table.

Comments

damien tournoud’s picture

damien tournoud commented

We could also consider being a bit smarter in how we query this table.

At least PostgreSQL and SQL Server support functional indexes, meaning indexes build on a computed column.

Suppose we implement prefix indexes based on this feature. The Schema API implementation of those database engines will create an index based on SUBSTRING(source, 1, 30).

Now also suppose that instead of querying on:

SELECT * FROM {locales_source} s WHERE s.source = :source AND s.context = :context AND s.textgroup = 'default'

We query on:

SELECT * FROM {locales_source} s WHERE SUBSTRING(s.source, 1, 30) = SUBSTRING(:source, 1, 30) AND s.source = :source AND s.context = :context AND s.textgroup = 'default'

This query will perform nearly as fast as before on MySQL (which will use the index on s.source that will return one row, then match the substring expression on this row. But on PostgreSQL and SQL Server, it will perform faster then before because the engine will be able to use the computed index to satisfy the query, virtually achieving the same performance as MySQL.

andypost’s picture

andypost
he/him
Primary language Russian
commented

Marked as duplicate #771718: (source, context) index is suboptimal

andypost’s picture

andypost
he/him
Primary language Russian
commented

i18n uses own textgroups so I think we should put textgroup into index and change query to

SELECT * FROM {locales_source} s WHERE s.textgroup = 'default' AND s.context = :context AND SUBSTRING(s.source, 1, 30) = SUBSTRING(:source, 1, 30) AND s.source = :source

Mostly this query used on multilingual sites so should improve performance

So this help to drop index in #803380: locales_source.location index

andypost’s picture

andypost
he/him
Primary language Russian
commented
Status: Active » Closed (duplicate)

Duplicates #851362: Add hash column to {locales_source} to query faster locale strings

Talkless’s picture

Talkless commented
Issue summary: View changes

Could someone explain why there are no index on locales_source.source? I do not quite follow.

I've noticed, with Devel modules help, that a lot of small locale queries takes up a lot of time.

Before (Devel module printout):

Executed 46 queries in 148.31 ms.

After adding:
CREATE INDEX locales_source_source_idx ON locales_source(source);
I get:

Executed 46 queries in 65.86 ms

Drupal 7.26 (with Lithuanian language) on Ubuntu Server 12.04.4 amd64 with Postgresql 9.2.4 (I should upgrade that...).
It's already with FileCache, APC opcode cache and EntityCache.