Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)

[Janusz Krzysztofik]

Martijn Tonies wrote:
> 
> Hi,
> 
> > You are not using any indicies, because there aren't any that could be
> > used in this query.
> > Try adding an index on (status,deleted)
> 
> I wonder: how many possible different values would such an index
> return?

mysql> select distinct status, deleted from urlword;
+--------+---------+
| status | deleted |
+--------+---------+
|    200 |       0 |
|    503 |       0 |
|    302 |       0 |
|      0 |       1 |
|    200 |       1 |
|    504 |       0 |
|    301 |       0 |
|      0 |       0 |
|    204 |       0 |
|    420 |       0 |
|      1 |       0 |
|    303 |       0 |
|    470 |       0 |
+--------+---------+
13 rows in set (6 min 55.94 sec)

mysql> select distinct status, deleted, origin from urlword;
...
23 rows in set (7 min 9.90 sec)

> If this is a (very) low value, won't the index make things
> slower (if it's being used) compared to a full table scan?

I guess these values are very low for a table with 46 milion records,
so I understand it is better not to use such indicies, right?

>
> With regards,
> 
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
> Server.
> Upscene Productions
> http://www.upscene.com
> 

Thanks, Martijn,

Janusz