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 <vincent.degroote@stripped> 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
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
