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]
