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 Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
