Re: Best practices for using MySQL index

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

 



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.

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. But becoming a master does not come over night. Try the website www.mysqlperformanceblog.com for more good solid tips on these topics.

Aschwin Wesselius

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[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