taxonomy_get_tree db index improvements [#43588]

the term_hierarchy table has two indices, one on tid, and one on parent. We should have onl yone key (tid, parent). This pair is unique in this table and the index make the query faster. This applies to all vocabs since an entry to term_hierarchy is mandatory.

Comment	File	Size	Author
#7	term_hierarchy_1.patch	2.01 KB	killes@www.drop.org
#7
#2	term_hierarchy.patch	2.03 KB	killes@www.drop.org
#2

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

Morbus Iff

they/them

CreditAttribution: Morbus Iff commented 6 January 2006 at 15:50

I did some tests on this at killes' request with NHPR.org's vocabularies, which have 8500 terms. Prior to this index change, a query on admin/node (which displays a dropdown of a category tree) was 439.8ms (this was a single hierarchy). Adding the index and then refreshing the page brought it down to 291.16.

Comment #2

killes@www.drop.org CreditAttribution: killes@www.drop.org commented 6 January 2006 at 17:32

Status:

Active

» Needs review

File	Size
term_hierarchy.patch	2.03 KB

Here's a patch to try.

Comment #3

jvandyk CreditAttribution: jvandyk commented 8 January 2006 at 00:47

When I make the change to the term_hierarchy table in mysql 4.1.12, I get a warning from phpmyadmin that says "PRIMARY and INDEX keys should not both be set for column 'tid'". killes said he based this on the term_node table, and when I look at that table structure in phpmyadmin -- surprise! -- the same warning is there. Could a mysql guru comment on that?

Comment #4

killes@www.drop.org CreditAttribution: killes@www.drop.org commented 8 January 2006 at 13:59

Ber was so nice to make some tests:

14:54 < berkes> killes, I did "ALTER TABLE term_hierarchy ADD PRIMARY KEY (tid,
parent)" on an emty and a 1000+ term database
14:54 < berkes> no problems.
14:55 < berkes> mysql Ver 14.7 Distrib 4.1.12, for pc-linux-gnu (i486) using readline 4.3

So I think that John was just seeing a case of "phpmyadmin trying to be helpfull".

Cvbge said:

13:54 < Cvbge> killes: there won't be any problems with postgres, but it's not clear whether the multicolumn index (from PK) will be used in the taxonomy.module queries. OTOH it's good to have PK on every table (needed for replication) so I'd +1 this patch

I can report that the index gets used.

Comment #5

moshe weitzman CreditAttribution: moshe weitzman commented 10 January 2006 at 06:09

Title:	taxonomy_get_tree should be faster	» taxonomy_get_tree db index improvements
Status:	Needs review	» Needs work

thats not phpmyadmin complaining, its mysql. see http://moodle.org/mod/forum/discuss.php?d=32375 for similar problem. not sure if this warning is serious, but we should avoid duplicate indices i think.

Comment #6

Gerhard Killesreiter CreditAttribution: Gerhard Killesreiter commented 10 January 2006 at 13:48

I can't access that url.

Comment #7

killes@www.drop.org CreditAttribution: killes@www.drop.org commented 10 January 2006 at 19:01

Status:

Needs work

» Reviewed & tested by the community

File	Size
term_hierarchy_1.patch	2.01 KB

I've re-rolled the patch.

I have considered Moshe's objection and have come to the conclusion that it is a different issue (if it is an issue, couldn't read what is written on that url). We use the same db structure on the term_node table and did not yet have any bug report on it. It might be a waste to have two indices and I have no objection to removing them, but this should happen in a separate patch for both tables.