-----Original Message----- From: Scott Fletcher Sent: Thursday, August 05, 2004 11:00 AM To: 'KSTrainee' Subject: RE: Using the Index (Tables)....
One more question. Does the column's name have to be in order by col1, col2 and col3? Or does it not matter which order, like col1, col3, col2, where the "select * from <<table>> where <col1, col3, col2>" do the searching? --snip-- CREATE INDEX part_of_name ON customer (name(10)); --snip-- Thanks, FletchSOD -----Original Message----- From: KSTrainee [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 10:17 AM To: Scott Fletcher Subject: AW: Using the Index (Tables).... if you have one or more indexes on a table, the query optimizer will pick the most valueable by itself. the most important thing about 'valueable' is the key's cardinality which describes the number of unique values within the key. imagine this table: col1 col2 A 1 A 2 A 3 ... A 999999 B 1 B 2 B 3 ... B 999999 a key on col1 won't get you anywhere because it will route to only two unique values. if you query this like "select * from mytable where col1 = 'A' and col2 = '3'" will mysql cause to ignore(!) you key because doing a full table scan with only a few seeks and a lot burst reads is faster that a lot of seeks and a few random reads. if you set the key to cover (col1,col2), your query will return the row in no time, as the key directly leads to the one single row. in general, you don't _have_ to do maintance of the keys. however, if you update/insert/delete a lot of rows, you should run 'ANALYZE TABLE mytable;' so the table's statistics get updated. the query optimizer will pick the most valueable keys from there... -----Ursprüngliche Nachricht----- Von: Scott Fletcher [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 5. August 2004 17:01 An: [EMAIL PROTECTED] Betreff: Using the Index (Tables).... I have one question. Once I create 2 or more indexes to a table, should I instruct MySQL to use which index in certain order? (Like use that 2nd index, not the 1st one). Another question, do I need to run the maintance on the index? If so, how? With IBM DB2, I had to run the index maintance weekly, so I wonder if MySQL do it automatically or do I have to do it manually. If so, how? Thanks, FletchSOD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
