List:General Discussion« Previous MessageNext Message »
From:Kevin Kotun Date:January 5 2001 6:22pm
Subject:Index Problem
View as plain text  

On Solaris 
We have a table with over 20,000,000 records.


it has a index on the concatenated primary key.

I then created another index on only one column.

The show index command shows an entry for the new index, but
it has a null value for the cardinality, the number of unique groups

Then when i do an explain on a select with only the column that i just created 
an index on in the where clause, the explain returns only the unique index as 
availble and then of shows the unique index 

Fellow mysql aficionados,

We here at Everglades National Park, USA
are using MySQL 3.22.26 with Solaris 2.6.

We have a table with over 20 Million records for our hydrologic data.

It has an index on the concatenated primary key.

I then create another index on a single column. mysql carried out the request to make the
index with
no errors. 

The show index commmand displays and entry for this new index, but 
the cardinality number (the number that shows how many unique groups this index has) is
null.

This column is also the fourth column of the concatenated primary key index.

However, since the response was still very slow I did an explain on my select statement.

and the explain returned only one index available, the original primary key index. 

Of course since this was the only one it "saw" it was the one it would try to use
to satisfy the select request.

QUESTION:  Is this a bug? I could not find it in the "known bugs" part of the online doc.
	   Am I doing something obviously wrong?

TerraThanks to anyone who tries to help...

Kevin Kotun 
Hydrologist
Everglades National Park
Thread
Index ProblemKevin Kotun5 Jan