MySQL Forums
Forum List  »  Performance

Re: Queries returning large number of rows
Posted by: Anthony Willard
Date: May 24, 2006 07:08AM

Jan Hansen wrote:

> > > Any index you create (even one with
> maximum
> > cardinality) won't help much if you ask it
> to
> > process millions of records. If all of your
> data
> > is available in your index, the scan takes
> place
> > against the index instead of the table
> itself, but
> > it's still a scan.
>
> With MyISAM tables I'm definitely seeing a huge
> performance increase when the table is small
> enough for the indexes to fit into the memory
> specified in 'key_buffer_size', which suggests
> it's able to use the index without having to go to
> the rows themselves.

If you have many tables using the MyISAM engine then the key buffer will contain the most recently used keys and not necessarily keys for the table/data you need. Performance won't be consistent.

As for going to the table, what I meant was that if all the elements (select, where, order by, etc) needed by your query are in the index, the query engine won't need to fetch data from the table. But any scans are done against the index - scans are still scans. Making your keys as compact as possible increases the performance of index scans.

Have you tried a single column index on the column? It may help in this scenario since the index won't contain other data that slows down the scan.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Queries returning large number of rows
1557
May 24, 2006 07:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.