MySQL - Select using INDEX error

Anon

Hello people,

I am doing a simple select * using a where clause that referes to a indexed filed. I would expect MySQL executing the query and takind advantage of the indexed field. MySQL instead seams not to agree with me.

[system is a MySQL 4.01 for Windows/Table is MyISAM]

This is what happen:

select * from this_MyISAM_table where indexedfield = 'thevalue'

Explain return that index is not used.

instead:

select indexedfiled from this_MyISAM_table where indexedfield = 'thevalue'

Explain return that index is used.

and obviously:

select indexedfiled,NOT-indexedfiled from this_MyISAM_table where indexedfield = 'thevalue'

Explain return that index is not used.

Is all this logical to you? Do I really have to think that Micosoft SQL handles indexes better than MySQL.

Cheers and thanks for your kind suggetions.

Anon

Have you run an analyse on your tables recently?
There's a note in the manual somewhere about the ratio of records in the index to total records that governs when the index is read.

Anon

Have you run an analyse on your tables recently?
Well the table is new! just created. And at the moment only holding something like 1.000 rows. Anyway, yes, I did run ANALYSE.

There's a note in the manual somewhere about the ratio of records in the index to total records that governs when the index is read.

I understand, well fact is that I am starting to think that EXPLAIN is returning an incorrect value by NOT saying that indexes are used.

[deleted]

Explain tells you exactly what mysql will do. If it says it won't use the index, it won't use the index.

Anon

Could it be somewhat relevant the fact that in the table I have a Textblob (mediumtext)and this is indexed in fulltext (along with a varchar 255) ?

Anon

sigh

The fact is: a full table scan is MUCH faster than an index scan, depending on your where clause.

If you are selecting 1/3 of the rows in table - an index is useless. If the table has only a few rows (1000) then an index scan might be too slow, etc.

At least in postgres you can tell the query engine to not use sequential scans or index scans for a query...

Chris

[deleted]

Uhm yes that would matter. :-)

fulltext indexes are used with the 'match' operator, and nothing else. (fulltext is near-useless btw)