MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:September 28 2004 7:16pm
Subject:Re: increasing mysql/table performance..
View as plain text  
It is possible to have too many indexes. Usually you tune your indexes to 
fit the majority of your queries. Definitely index the fields that are 
used to JOIN your tables. Consider multi-column indexes more than lots of 
single-column indexes as MySQL will use only one index per table for any 
query. The order you use to list the columns in a multi-column index makes 
a huge difference.

I have lots of suggested reading for you:

http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html
http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html (the whole 
chapter)

Come back and we can help explain whatever didn't make any sense.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Jeff Smelser <tradergt@stripped> wrote on 09/28/2004 02:19:29 PM:

> On Tuesday 28 September 2004 01:02 pm, Ed Lazor wrote:
> > I usually create an index for each criteria being checked against in 
the
> > SQL statements.
> >
> > For example, for this query
> >
> > Select * from products where ProductID = 'aeg8557'
> >
> > I'd create an index on ProductID.  The same thing applies if you're 
pulling
> > data from multiple tables.
> >
> > For this query:
> >
> > Select products.ID, products.Title, categories.Title from products,
> > categories where products.ID = '5' AND products.CategoryID = 
categories.ID
> >
> > I'd make sure that products.ID, products.CategoryID, and categories.ID 
all
> > have an index.
> 
> Um.. Are you serious? thats all you do, create indexes?
> 
> Jeff
> [attachment "attonuto.dat" deleted by Shawn Green/Unimin] 
Thread
increasing mysql/table performance..bruce28 Sep
  • RE: increasing mysql/table performance..Ed Lazor28 Sep
  • Re: increasing mysql/table performance..Jeff Mathis28 Sep
Re: increasing mysql/table performance..Jeff Smelser28 Sep
  • RE: increasing mysql/table performance..Ed Lazor28 Sep
  • Re: increasing mysql/table performance..SGreen28 Sep
RE: increasing mysql/table performance..bruce28 Sep
  • RE: increasing mysql/table performance..Ed Lazor28 Sep
RE: increasing mysql/table performance..SGreen28 Sep