List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 24 2003 5:15pm
Subject:Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)
View as plain text  
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

Thread
Big difference in MyISAM and InnoDB SELECT speedJanusz Krzysztofik24 Nov
RE: Big difference in MyISAM and InnoDB SELECT speedMechain Marc24 Nov
  • Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Janusz Krzysztofik24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)gerald_clark24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Martijn Tonies24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Janusz Krzysztofik24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Martijn Tonies24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Janusz Krzysztofik27 Nov
RE: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Mechain Marc24 Nov