Best practices for using MySQL index

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...ng-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

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...ng-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

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

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

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...ng-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

>> 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. :)

To explain that further the idea is that if you have something like a
'status' field which can only hold 5 values, there's no point indexing
it if there's a reasonably even spread.

If you could only ever have a handful of fields with a status code of
'1', then it's worth indexing if you have to find those particular
records quickly. I don't think mysql supports partial indexes, but some
databases do so you only index the fields that match a certain criteria.

I'd suggest a more thorough approach to working out what to index rather
than just trying to guess what's going on.

Work out how long queries are taking (either use the mysql slow log or
if you're using a database abstraction class, it should be easy enough
to hack in) and concentrate on those first.

http://www.designmagick.com/article/...dex-a-database

(While it's on a postgresql site, there's nothing specifically for
postgresql in that article - the same rules apply to mysql, oracle, mssql).

On Wednesday 30 April 2008, Chris wrote:
> >> 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. :)
>
> To explain that further the idea is that if you have something like a
> 'status' field which can only hold 5 values, there's no point indexing
> it if there's a reasonably even spread.
>
> If you could only ever have a handful of fields with a status code of
> '1', then it's worth indexing if you have to find those particular
> records quickly. I don't think mysql supports partial indexes, but some
> databases do so you only index the fields that match a certain criteria.
>
> I'd suggest a more thorough approach to working out what to index rather
> than just trying to guess what's going on.

Another piece of low-hanging-fruit is to index a field that you will be
joining on frequently. Point above about spread still applies, but if you
can join index to index, the join goes a lot faster. (A primary key in MySQL
is always indexed.)

Having too many indexes rarely if ever costs on read (as far as I am aware),
but it does cost on write to update the index. How much that matters is
use-case specific.

--
Larry Garfield AIM: LOLG42
larry@garfieldtech.com ICQ: 6817012

"If nature has made any one thing less susceptible than all others of
exclusive property, it is the action of the thinking power called an idea,
which an individual may exclusively possess as long as he keeps it to
himself; but the moment it is divulged, it forces itself into the possession
of every one, and the receiver cannot dispossess himself of it." -- Thomas
Jefferson

On Wed, Apr 30, 2008 at 7:03 PM, Aschwin Wesselius <aschwin@illuminated.nl>
wrote:

> 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.
>
I think I missed that part. For I am concerning query, index, and entity
design most of the time.

>
>
> Aschwin Wesselius
>



--
Regards,
Shelley

On Thu, May 1, 2008 at 9:54 AM, Larry Garfield <larry@garfieldtech.com>
wrote:

> On Wednesday 30 April 2008, Chris wrote:
> > >> 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. :)
> >
> > To explain that further the idea is that if you have something like a
> > 'status' field which can only hold 5 values, there's no point indexing
> > it if there's a reasonably even spread.
> >
> > If you could only ever have a handful of fields with a status code of
> > '1', then it's worth indexing if you have to find those particular
> > records quickly. I don't think mysql supports partial indexes, but some
> > databases do so you only index the fields that match a certain criteria.
> >
> > I'd suggest a more thorough approach to working out what to index rather
> > than just trying to guess what's going on.
>
> Another piece of low-hanging-fruit is to index a field that you will be
> joining on frequently.

The fact is, we seldom use join. Because we think it very slow to make it
working on two tables
with millions of records. Though we did not try it. :(

> Point above about spread still applies, but if you
> can join index to index, the join goes a lot faster. (A primary key in
> MySQL
> is always indexed.)
>
How much is the *a lot*? Thanks. :)

>
> Having too many indexes rarely if ever costs on read (as far as I am
> aware),
> but it does cost on write to update the index. How much that matters is
> use-case specific.
>
> --
> Larry Garfield AIM: LOLG42
> larry@garfieldtech.com ICQ: 6817012
>
> "If nature has made any one thing less susceptible than all others of
> exclusive property, it is the action of the thinking power called an idea,
> which an individual may exclusively possess as long as he keeps it to
> himself; but the moment it is divulged, it forces itself into the
> possession
> of every one, and the receiver cannot dispossess himself of it." --
> Thomas
> Jefferson
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--
Regards,
Shelley

>> Point above about spread still applies, but if you
>> can join index to index, the join goes a lot faster. (A primary key in
>> MySQL
>> is always indexed.)
>>
> How much is the *a lot*? Thanks. :)

If it's a unique (including primary) key then orders of magnitude for
millions of rows.

If it's a non-unique key, it depends on how many distinct values there are.

--
Postgresql & php tutorials
http://www.designmagick.com/