List:General Discussion« Previous MessageNext Message »
From:mos Date:July 9 2010 2:31pm
Subject:Re: combined or single indexes?
View as plain text  
At 12:43 AM 7/9/2010, you wrote:
>On Fri, Jul 9, 2010 at 7:30 AM, Neil Tompkins
><neil.tompkins@stripped>wrote:
>
> >
> > Should we be looking to create a index for all fields that we might be
> > perform a select condition in a where clause for ?

Neil,
      Keep in mind that the more indexes you add to a table, the slower 
your inserts will become because it needs to maintain the indexes. I would 
only consider adding indexes to the slow queries. You can use the Explain 
on your Select statement to see which indexes are being used. See 
http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

for an explanation on how to use EXPLAIN.

BTW, if you are indexing text columns, you may want to look at FullText 
indexing.

Mike



>It's a bit of trial and error, you have to weight the number of times you
>select based on a potential index vs. the impact that index might have for
>the amount of updates you do on the table.
>
>Generally speaking, though, it's indeed a good idea to find the
>most-frequently used where-clauses and index them.
>
>
>--
>Bier met grenadyn
>Is als mosterd by den wyn
>Sy die't drinkt, is eene kwezel
>Hy die't drinkt, is ras een ezel

Thread
combined or single indexes?Bryan Cantwell6 Jul
  • Re: combined or single indexes?Joerg Bruehe6 Jul
  • Re: combined or single indexes?Octavian Rasnita7 Jul
    • Re: combined or single indexes?Neil Tompkins8 Jul
      • Re: combined or single indexes?Johan De Meersman9 Jul
        • Re: combined or single indexes?Neil Tompkins9 Jul
          • Re: combined or single indexes?Johan De Meersman9 Jul
            • Re: combined or single indexes?mos9 Jul
              • Re: combined or single indexes?Tompkins Neil21 Jul
                • Re: combined or single indexes?MySQL)22 Jul
                  • Re: combined or single indexes?Tompkins Neil22 Jul
                    • RE: combined or single indexes?Jerry Schwartz22 Jul
                      • Re: combined or single indexes?Tompkins Neil22 Jul
                        • RE: combined or single indexes?Jerry Schwartz22 Jul
                          • Re: combined or single indexes?Neil Tompkins23 Jul