> 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]
