Hi,
I have a table with 18 million of rows. The table structure is
describe user_att
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| user_id | int(11) | | PRI | 0 | |
| att_id | int(11) | | PRI | 0 | |
| value | varchar(200) | | | | |
| date | datetime | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
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)
My question is
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?
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?
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 = '?'
Thanks
Hsiu-Hui
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
