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