Re: Index Problem on 3.23.20, with Large Query

[Dan Nelson]

In the last episode (Jul 06), Joe Kislo said:
> 	The query should return ~26,623 rows.
> 
> mysql> select count(*) from Item;
> +----------+
> | count(*) |
> +----------+
> |    46116 |
> +----------+
> 1 row in set (0.00 sec)
> 
> Is there a limitation on how many rows mysql can return when using an
> index?  Do you know what limitation we're running into here?

That might be the problem.  When you're querying a single table, and
your result set is 1/2 your entire table volume, it's usually a lot
more efficient to walk the table sequentially and ignore the records
that don't satisfy the query rather than walk an index, and for each
record, seek to a random position in the database to fetch the data.  I
think MySQL's rule of thumb is if your query returns more than 20% of
the total database size, it's quicker to do a full table scan.  When
you pare your query down to return only 1000 rows, it dips under the
20% threshhold and starts using the index.

If you're sure that using an index is faster, you can try hinting with
the "USE INDEX (key_list)" clause.

-- 
	Dan Nelson
	dnelson@stripped