I really don't know if this is a bug or feature request, but I thought that it should be worth an "issue".

The function uses "lower" on a like... but using a function in a where, disables the indexes...
I can remove the "LOWER", being on a mysql, but I also found that with MySQL using the sintax like " 'xxxx' LIKE name " instead of " name LIKE 'xxxxx' " will bypass the index on the name field. This is a mjor performarce problem (I've near a million of tags).

  $db_result = db_query(db_rewrite_sql("SELECT t.tid, t.* FROM {term_data} t WHERE LOWER('%s') LIKE LOWER(t.name)", 't','tid'), trim($name));

should read

  $db_result = db_query(db_rewrite_sql("SELECT t.tid, t.* FROM {term_data} t WHERE LOWER(t.name) LIKE LOWER('%s')", 't','tid'), trim($name));

(My actual code does not use LOWERs)

I've also added these EXPLAINs (look the "possible_keys" field): http://pastie.caboo.se/96865

Comments

flexer’s picture

flexer commented

Also Postgres follows the "not cummutative" LIKE behaviour

tyrell.com’s picture

tyrell.com commented

Aren't MySQL SQL patterns case-insensitive anyway?

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

So the query should be able to be reduced to

$db_result = db_query(db_rewrite_sql("SELECT t.tid, t.* FROM {term_data} t WHERE t.name LIKE '%s'", 't','tid'), trim($name));
catch’s picture

catch
he/him
Primary language English
commented
Status: Active » Closed (duplicate)

This issue is a bit older, but there's more action in #277209: Remove lower from taxonomy autocomplete so marking as duplicate.