We have quite a lot of users and quite a lot of tags (abut 14,000 users + 150 tags)... This means the term_user table is going to get quite large. Even with only 60 or so rows, the table became VERY slow.

I took a look and found that there wasn't even a primary key!

The install file needs to have an update to add at least a join primary key on the uid and tid fields (this forces the combination of user and term to be unique). It would also be handy to have a separate index on the column which appears as the 2nd in the primary key pair.

This tiny addition to the table reduced one of my queries from 2.2s down to 0.07s!

Comments

flk’s picture

flk commented
Status: Active » Fixed

cheers, done

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)
deekayen’s picture

deekayen commented

Is there a particular reason the keys were put in the order they were (tid, uid)? cYu did some testing and found (uid, tid) made much faster queries in our case.

/**
 * Implementation of hook_update_N().
 */
function user_tags_update_5000() {
  $ret = array();
  $ret[] = update_sql("ALTER TABLE {term_user} ADD PRIMARY KEY ('uid','tid')");
  return $ret;
}