You need do  SHOW INDEXES FROM <tbl-name>;

You will see a column called Cardinlaity (another word for a unique count).
This tells you how many distinct values exists at the given Seq_in_index for 
the index.

If the Cardinality of an index is low in relation to the number of rows in the 
table,
then MySQL may decide not to use the index if the number of rows expected to 
return
is a sizeable percentage of the number of rows in the table.

Example: 3 month query bring back 2000000 rows.
Total rows ion the table: 28000000.
2000000 / 28000000 = 0.07142857142857... = 7.143 %
Your 3 month query retrieves 7.143% of the data.

Think about:
Queryiong 3 months retrieves 7.143  % of the data.
Theoretically,  6 months should retrieve 14.286% ( 4000000 rows)
Theoretically, 12 months should retrieve 28.571% ( 8000000 rows)
Theoretically, 18 months should retrieve 42.857% (12000000 rows)
Theoretically, 24 months should retrieve 57.143% (16000000 rows)

The closer the cardinality of your query results is to the
cardinality of the table, 

The larger the data range, the larger the number of rows retrieved.
More than likely, MySQL decided that a table scan would be faster
and resource economical that doing a heavy index scan.

You need run this query
SELECT (28000000/COUNT(DISTINCT date)) RowsPerDate FROM <tbl-name>;
This will tell you the average number of rows in the table per date.

You may want to add more columns to the index and your query also.

A single index with just a date is not good when the cardinality of the date is 
low.

----- Original Message -----
From: "Peter" <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Tuesday, March 20, 2007 9:24:25 AM (GMT-0500) Auto-Detected
Subject: indexes and size

Hello,


I have a a large a table which a field called date, type date.

When I select a smaller range e.g 3 months system uses the index 'date'.
That is for let's say 2 million rows.

If I select wider date range mysql stops using key. It says possible key
"date", but do not use it and goes over all 28 Million rows.

If I use "force index" the query becomes even slower.

Table type is Myisam.

Please advise what should I tune so mysql uses the index without force
index and query becomes faster.


Thanks :-)

Peter


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to