Re: Best practices for using MySQL index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Apr 30, 2008 at 5:14 PM, Aschwin Wesselius <aschwin@xxxxxxxxxxxxxx>
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

[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux