Prev: fourat wants to keep up with you on Twitter
Next: [PHP-DB]problem in creating the ibm_db2 extension
From: Shelley on 30 Apr 2008 00:54
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.

--
Regards,
Shelley
From: Aschwin Wesselius on 30 Apr 2008 05:14
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
From: Shelley on 30 Apr 2008 06:08
On Wed, Apr 30, 2008 at 5:14 PM, Aschwin Wesselius <aschwin(a)illuminated.nl>
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
From: Aschwin Wesselius on 30 Apr 2008 07:03
Shelley wrote:
>> 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. :)


Well, to give you a good measure: keep the cardinality between 30 to
70-80 percent of your total records in a column. But sometimes your
field is NULL or empty, so it really depends. You can't just put it into
a standard configuration. And it also really depends on how many records
a table contains etc.

Besides that, benchmarking your development environment (you do have one
do you?) can gives you a good idea on how your hardware and setup performs.

Aschwin Wesselius
From: Robert Cummings on 30 Apr 2008 11:06

On Wed, 2008-04-30 at 11:14 +0200, Aschwin Wesselius 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.

Could you describe a well designed DB that contains searchable text that
doesn't contain a text index... fulltext or otherwise.

Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP

 |  Next  |  Last
Pages: 1 2
Prev: fourat wants to keep up with you on Twitter
Next: [PHP-DB]problem in creating the ibm_db2 extension