On Wed, Apr 30, 2008 at 5:14 PM, Aschwin Wesselius <[EMAIL PROTECTED]>

> 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


Reply via email to