Hi, >From the manual (http://www.mysql.com/doc/en/MySQL_indexes.html):
Fabrizio Nesti wrote: > Hello, it may be a simple question, but I can't get to it: > > Why this simple query does not use the PRIMARY key (which is oai_ui)? > > mysql> explain select * from idb_metadata where oai_ui like 'doi:10.1088/0%'; > +--------------+------+---------------+------+---------+------+--------+------------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra | > +--------------+------+---------------+------+---------+------+--------+------------+ > | idb_metadata | ALL | PRIMARY | NULL | NULL | NULL | 178035 | where used | > +--------------+------+---------------+------+---------+------+--------+------------+ > "If the use of the index would require MySQL to access more than 30% of the rows in the table. (In this case a table scan is probably much faster, as this will require us to do much fewer seeks.)...." > While this is correctly using it.... > > mysql> explain select oai_ui from idb_metadata where oai_ui like 'doi:10.1088/0%'; > >+--------------+-------+---------------+---------+---------+------+--------+-------------------------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra > | > >+--------------+-------+---------------+---------+---------+------+--------+-------------------------+ > | idb_metadata | range | PRIMARY | PRIMARY | 50 | NULL | 137387 | where >used; Using index | > >+--------------+-------+---------------+---------+---------+------+--------+-------------------------+ > "In some cases a query can be optimised to retrieve values without consulting the datafile. If all used columns for some table are numeric and form a leftmost prefix for some key, the values may be retrieved from the index tree for greater speed:" > The change is just that I select * instead of the column itself. > > Thanks in advance to whoever can help.. > running crazy in tuning, > Thanks > > Fabrizio Nesti -- MediaLab -- SISSA -- IT > > > > > PS: System info: > > Server: 3.23.38-max-log > Table desc (it has 178035 records): > mysql> desc idb_metadata; > +-------------+---------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +-------------+---------------+------+-----+---------+-------+ > | oai_ui | varchar(50) | | PRI | | | > | download | date | YES | | NULL | | > | datestamp | date | YES | | NULL | | > | updated | timestamp(14) | YES | | NULL | | > | submission | date | YES | MUL | NULL | | > | replacement | date | YES | MUL | NULL | | > | title | text | YES | | NULL | | > | abstract | text | YES | | NULL | | > | comment | text | YES | | NULL | | > | journal | tinytext | YES | | NULL | | > | url | tinytext | YES | | NULL | | > | type | tinytext | YES | | NULL | | > | language | tinytext | YES | | NULL | | > | rights | tinytext | YES | | NULL | | > | publisher | tinytext | YES | | NULL | | > | contributor | tinytext | YES | | NULL | | > | format | tinytext | YES | | NULL | | > | source | tinytext | YES | | NULL | | > | relation | tinytext | YES | | NULL | | > | coverage | tinytext | YES | | NULL | | > | profiles | tinyint(4) | YES | | 1 | | > +-------------+---------------+------+-----+---------+-------+ > > > Hope this Helps -- Joseph Bueno --------------------------------------------------------------------- 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
