Hello.
> This index is not used at all for the select ? substring(segment_0_sk,1,677) - a function is applied to the indexed column. In my opinion, MySQL isn't so smart to understand that this is a prefix. If you want to order only on the prefix of your text column, think about max_sort_length server system variable (it is a session variable as well). See: http://dev.mysql.com/doc/mysql/en/server-system-variables.html Vincent De Groote <[EMAIL PROTECTED]> wrote: > Hello, > > I have the following table: > > CREATE TABLE `list_datas_1` ( > `list_id` bigint(20) NOT NULL, > `locale_id` bigint(20) NOT NULL, > `record_id` bigint(20) NOT NULL, > `segment_0` longtext, > `segment_1` longtext, > `segment_0_sk` longblob, > `segment_1_sk` longblob, > KEY `record_id` (`record_id`), > KEY `list_id` (`list_id`), > KEY `datas` (`locale_id`,`segment_0_sk`(677),`segment_1_sk`(338)), > CONSTRAINT `list_datas_1_ibfk_1` FOREIGN KEY (`list_id`) REFERENCES > `list_definitions` (`list_definition_id`), > CONSTRAINT `list_datas_1_ibfk_2` FOREIGN KEY (`locale_id`) REFERENCES > `locales` (`locale_id`), > CONSTRAINT `list_datas_1_ibfk_3` FOREIGN KEY (`record_id`) REFERENCES > `records` (`record_id`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > When I use the following statement: > > select record_id, segment_0_sk, segment_1_sk from list_datas_1 order by > locale_id, substring(segment_0_sk,1,677); > > explain returns the following datas: > > +----+-------------+--------------+------+---------------+------ > +---------+------+------+----------------+ > | id | select_type | table | type | possible_keys | key | > key_len | ref | rows | Extra | > +----+-------------+--------------+------+---------------+------ > +---------+------+------+----------------+ > | 1 | SIMPLE | list_datas_1 | ALL | NULL | NULL | NULL > | NULL | 2697 | Using filesort | > +----+-------------+--------------+------+---------------+------ > +---------+------+------+----------------+ > > The two columns in the order by clause is a prefix of the 'datas' index. > This index is not used at all for the select ? > > Thanks for you replies. > > vdg > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
