1

Possible Duplicate:
Is it possible to have function-based index in MySQL?

I have a table with a string field s and I want to run this query:

SELECT * FROM mytable WHERE s = LEFT("färgkört", CHAR_LENGTH(s));

Its explanation clearly says that I am not using the index on that field:

+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | mytable      | ALL  | NULL          | NULL | NULL    | NULL | 766554 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+

Is there any way to optimize a query like this?

Community
  • 1
  • 1
MadMage
  • 186
  • 1
  • 7
  • Review: http://stackoverflow.com/questions/10595037/is-it-possible-to-have-function-based-index-in-mysql asked & answered. MySQL doesn't support function based indexes. but it will support leading indexes for fixed length of characters. `create index name_first_five on cust_table (name(5));` – xQbert Oct 15 '12 at 10:53
  • @MadMage Why do you want this? – Devart Oct 15 '12 at 11:14
  • The word "färgkört" is a compound, it is composed by "färg" and "kört". I have the latter two on mytable and I have to understand that "färgkört" is the compound of them. I run this query, sort by length descending and I find "färg", I remove (not in MySQL) it from the word and then I search for "kört". Any advices on alternatives on how to do this are welcome as well :-) – MadMage Oct 15 '12 at 11:25
  • In this case you could use LOCATE() function, but it won't improve performance either. – Devart Oct 15 '12 at 11:37
  • If the words were separated by certain delimiter character (' ', ',', '.'), then you could use FULLTEXT searching. – Devart Oct 15 '12 at 11:47
  • @Devart I think the use of LOCATE lowers the performance, since I am looking for **s** that share the same _prefix_ with my word, I do not need to search elsewhere in the string... and unfortunately the words are not delimited (we could blame German or Swedish languages for this, but I think it's not fair to blame something because it's difficult to mechanize :-)). – MadMage Oct 15 '12 at 12:22

0 Answers0