MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:September 28 2004 7:40pm
Subject:RE: increasing mysql/table performance..
View as plain text  
Primary Keys and Unique Keys are still Keys. Key is a synonym for INDEX. 
So, you already have some indexes. 

What you need to look at is the possibility of defining other "regular" 
keys (just plain indexes) to help specifically with some of your common 
query situations. Some people also add indexes to solve critical 
performance hits during some very important queries even if they aren't 
executed often at all.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"bruce" <bedouglas@stripped> wrote on 09/28/2004 03:28:54 PM:

> shawn...
> 
> in my tables... i usually try to create a unique key that's used to link
> with the other tbls...
> 
> does creating an index within a table that's already consisting of 
unique
> rows give any benefits..???
> 
> thanks...
> 
> 
> 
> -----Original Message-----
> From: SGreen@stripped [mailto:SGreen@stripped]
> Sent: Tuesday, September 28, 2004 12:17 PM
> To: Jeff Smelser
> Cc: mysql@stripped
> Subject: Re: increasing mysql/table performance..
> 
> 
> 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