taxonomy_get_term_by_name() query doesn't use an index [#659570]

Well, basically, like the title says.

In both D6 and D7, the final query taxonomy_get_term_by_name() sends to the database is similar to:

SELECT t.tid, t.* FROM term_data t WHERE t.name = 'test';

The name field doesn't have an index on it. The attached patch adds an index on the 'name' field.

Comment	File	Size	Author
	taxonomy_term_data_add_name_index.patch	1.04 KB	brianV

Comments

Comment #1

brianV commented 14 December 2009 at 18:30

Just to clarify, example query is from D6, and should rightly be:

D6:

SELECT t.tid, t.* FROM term_data t WHERE LOWER(t.name) LIKE LOWER('%test');

D7's query is a bit more complex, with a few JOINS, but still needs the index on the 'name' column.

Comment #2

Anonymous (not verified) commented 14 December 2009 at 19:48

There are already 2 indexes on the name column, but they're not being used by these queries because the fields are 2nd and 3rd order in a multi-column index.

Do we need an index on name alone? Or can we swap the order of the vid_name index to put name first? If we can't swap, should we separate the vid_name index to be 2 indexes on both fields separately?

We need some DB folks on this. Thanks for bringing it up, brianV.

Comment #3

catch

he/him

English

commented 15 December 2009 at 07:03

Issue tags:

+Performance

The reason it doesn't have an index in D6 is because LOWER() queries never use an index anyway. See #279851: Replace LOWER() with db_select() and LIKE() where possible for background.

However now we use LIKE() in D7, and this doesn't use wildcards either, so it should use an index - very nice find.

I'm not aware of anywhere in core where the vid, name index is used - I'd rather see us drop that as well as using the name one (unless I've got that wrong).