Re: Queries returning large number of rows
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.
Subject
Views
Written By
Posted
5500
May 22, 2006 06:21AM
1891
May 22, 2006 09:04AM
1726
May 22, 2006 10:28AM
1747
May 23, 2006 07:13AM
1775
May 23, 2006 01:02PM
Re: Queries returning large number of rows
1557
May 24, 2006 07:08AM
1500
May 24, 2006 09:19AM
1645
May 24, 2006 10:18AM
1715
May 24, 2006 11:53AM
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.