select count(), keys und left() ?

[alexander newald]

Code: Select all

mysql> select count(id) from test where left(id,1) = "d";
+---------------+
| count(id)     |
+---------------+
|             0 |
+---------------+
1 row in set (1.83 sec)

mysql> explain select count(id) from test where left(id,1) = "d";
+---------+-------+---------------+--------+---------+------+-------+-------------------------+
| table   | type  | possible_keys | key    | key_len | ref  | rows  | Extra 
|
+---------+-------+---------------+--------+---------+------+-------+-------------------------+
| test    | index | NULL          | id     |     256 | NULL | 80352 | where 
used; Using index |
+---------+-------+---------------+--------+---------+------+-------+-------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|    80352 |
+----------+
1 row in set (0.00 sec)

Was muss ich ändern, damit die Verwendung von keys hier was bringt?

[stefanpropehan]

Von welchen Typ ist die Spalte "id"?

Sollte es der Typ text oder varchar sein solltest du mal den Index Typ Fulltext versuchen...

Stefan