List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:July 21 2010 5:02pm
Subject:Re: combined or single indexes?
View as plain text  
Hi

So Just running a basic query I get returned the following :

table,type,possible_keys,key,key_len,ref,rows,Extra,
Products,ALL,,,,,9884,where used,

Therefore, I assume "*ALL*" is the worst possible type and should look at
adding a an index to this particular field ?

Cheers
Neil


On Fri, Jul 9, 2010 at 3:31 PM, mos <mos99@stripped> wrote:

> 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.htmfor
> 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
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>

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