From: Martijn Tonies
Date: November 24 2003 5:15pm
Subject: Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB   SELECT speed)
List-Archive: http://lists.mysql.com/mysql/154400
Message-Id: <001b01c3b2ae$98bd9200$0e02a8c0@martijn>
MIME-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-2"
Content-Transfer-Encoding: 7bit

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?

Well, that's what I have understood from many db engines :-)

Of course, the engine itself should be smart enough to avoid using the index
because of low selectivity.

The idea is, that fetching rows in index order takes make time then fetching
rows in storage order.

With regards,

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