select count(), keys und left() ?

MySQL, PostgreSQL, SQLite
alexander newald
Posts: 1117
Joined: 2002-09-27 00:54
Location: Hannover

select count(), keys und left() ?

Post by alexander newald » 2004-09-23 17:09

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
Posts: 335
Joined: 2002-12-17 22:25
Location: Berlin

Re: select count(), keys und left() ?

Post by stefanpropehan » 2004-09-23 17:42

Von welchen Typ ist die Spalte "id"?

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

Stefan