Hi,
how big is the result set in your 2 queries (compared to the number of
rows in the table)?
Seems that in the 2nd case the query was much more selective than in the
first case. Therefore using an index in the first query might have been
considered not advantageous.
But try this also with 'select k1,k2' instead of 'select *' !
Regards,
Frank.
Maciek Dobrzanski wrote:
>
> Hi,
>
> This happens on MySQL 3.23.44
>
> CREATE TABLE ttble (
> `k1` varchar(100) NOT NULL default '',
> ...
> `k2` varchar(65) NOT NULL default '',
> ...
> PRIMARY KEY (`k1`, `k2`)
> ...
> ) TYPE=InnoDB
>
> When k1 is compared to a string with only one known letter...
>
> EXPLAIN SELECT * FROM ttble WHERE k1 LIKE 'a%' AND k2 = 'string';
> +-------+------+---------------+------+---------+------+---------+----------
> --+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
> +-------+------+---------------+------+---------+------+---------+----------
> --+
> | ttble | ALL | PRIMARY | NULL | NULL | NULL | 1206398 | where
> used |
> +-------+------+---------------+------+---------+------+---------+----------
> --+
>
> ...but when more letters are given...
>
> EXPLAIN SELECT * FROM ttble WHERE k1 LIKE 'ab%' AND k2 = 'string';
> +-------+-------+---------------+---------+---------+------+------+---------
> ---+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
> +-------+-------+---------------+---------+---------+------+------+---------
> ---+
> | ttble | range | PRIMARY | PRIMARY | 165 | NULL | 5112 | where
> used |
> +-------+-------+---------------+---------+---------+------+------+---------
> ---+
>
> Is this a bug?
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Dr. Frank Ullrich, Netzwerkadministration
Verlag Heinz Heise GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover
E-Mail: [EMAIL PROTECTED]
Phone: +49 511 5352 587; FAX: +49 511 5352 538
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
