Stephan,
Wednesday, March 06, 2002, 1:24:37 PM, you wrote:
SB> We are running a mysql database version 3.22.32.
SB> I have an simple table:
SB> id, int(10) unsigned, PRI, default 0, auto_increment
SB> usernummer, int(10) unsigned, default 0
SB> productcode, char(10), default ''
SB> monat, tinyint(3) unsigned, default 0
SB> jahr, mediumint(8) unsigned, MUL, 0
SB> and two indizes:
SB> mysql> show index from reporting;
SB>
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+
SB> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
SB> Collation | Cardinality | Sub_part |
SB>
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+
SB> | reporting | 0 | PRIMARY | 1 | id |
SB> A | 3689 | NULL |
SB> | reporting | 1 | jahr_idx | 1 | jahr |
SB> A | NULL | NULL |
SB>
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+
SB> when i commit this select:
SB> 'SELECT * FROM reporting WHERE jahr=2002;'
SB> it seems that the index isn't hit.
SB> mysql> explain select * from reporting where jahr=2002;
SB> +-----------+------+---------------+------+---------+------+------+------------+
SB> | table | type | possible_keys | key | key_len | ref | rows |
SB> Extra |
SB> +-----------+------+---------------+------+---------+------+------+------------+
SB> | reporting | ALL | jahr_idx | NULL | NULL | NULL | 3689 |
SB> where used |
SB> +-----------+------+---------------+------+---------+------+------+------------+
SB> 1 row in set (0.00 sec)
SB> Can someone tell me why the index is not hit???
In some cases MySQL doesn't use indexes. If result of query is more
than 30% rows in a table, MySQL won't use indexes. It works much
faster without index usage.
You can read about how MySQL uses indexes at:
http://www.mysql.com/doc/M/y/MySQL_indexes.html
SB> Stephan
--
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
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
