> 2 index on this table: > - one unique index on user_id and att_id (pk) > - one index on att_id and user_id. > > I need to have the following query: > > select value from user_att where att_id = ? and value like '?' > (no wildcard) > 1. when I do a explain, this query use the second index. But, if > I change my > second index to att_id and value, will the performance improve?
You could add it as a third index and see which works better, but on 18 million rows that's going to probably take quite a bit of time. From looking at your query it seems like it would be a better index than the current one. > 2. what is the difference if I change the query to > select value from user_att where att_id = ? and lower(value) = > lower('?') > will this query slower? I could be wrong, but I believe the query won't use the index if you use lower(). Run an explain on this query and see. > 3. when compare string, is mysql sql case sensitive? It seems that it is > case in-sensitive. If case in-sensitive, the following query will > be faster? > select value from user_att where att_id = ? and value = '?' Mysql is only case sensitive on binary and blob fields. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]