Re: Best practices for using MySQL index

On Wed, Apr 30, 2008 at 5:14 PM, Aschwin Wesselius <aschwin@illuminated.nl> wrote: > Shelley wrote: > > > Hi all, > > > > I am currently responsible for a subscription module and need to design > > the > > DB tables and write code. > > > > I have described my table design and queries in the post: > > > > > > http://phparch.cn/index.php/mysql/38-MySQL-configuration/152-best-practices-for-using-mysql-index > > > > The problem is, in a short time the table will hold millions of records. > > So the query and index optimization is very important. > > > > Any suggestion will be greatly appreciated. > > > Hi, > > While this is not a MySQL mailing list, I try to give you some hints and > keep it short. > > Index on most integer fields only. Text fields can be indexed, but is not > important when you design your DB well. > > Don't index just all integer fields. Keep track of the cardinality of a > column. If you expect a field to have 100.000 records, but with only 500 > distinct values it has no use to put an index on that column. A full record > search is quicker. Hmmm... That's new. :) > > > Put the columns with the highest cardinality as the first keys, since > MySQL will find these if no index is explicitly given. > > You can look at an index with "SHOW INDEX FROM table" and this gives you a > column "cardinality". > > Try out your select statements and use "EXPLAIN SELECT <whatever> FROM > table" and use some joins on other tables. This will show you which possible > indexes are found and which one is being used for that query. You can > sometimes force or ignore an index being used like this "SELECT <whatever> > FROM table USE INDEX (userID)". Try the MySQL manual for more options. But > do use the "EXPLAIN" statement to have a close look on the use of indexes > and the use of sorting methods. Because both are important. Having a good > index, but a slow sorting method won't get you good results. > > I hope this is a good short hint on using indexes. Yes. It is. > But becoming a master does not come over night. Try the website > www.mysqlperformanceblog.com for more good solid tips on these topics. Good link. Thanks. > > > Aschwin Wesselius > -- Regards, Shelley