List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:October 7 2011 6:58pm
Subject:Re: MySQL Indexes
View as plain text  
Do you have any good documentation with regards creating indexes. Also information for
explain statement and what would be the desired result of the explain statement?

On 7 Oct 2011, at 17:10, Michael Dykman <mdykman@stripped> wrote:

> How heavily a given table is queried does not directly affect the index size, only
> the number and depth of the indexes.
> 
> No, it is not that unusual to have the index file bigger.  Just make sure that every
> index you have is justified by the queries you are making against the table.
> 
>  - md
> 
> 
> On Fri, Oct 7, 2011 at 4:26 AM, Tompkins Neil <neil.tompkins@stripped>
> wrote:
> Is it normal practice for a heavily queried MYSQL tables to have a index file bigger
> than the data file ?
> 
> 
> On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman <mdykman@stripped> wrote:
> Only one index at a time can be used per query, so neither strategy is optimal.  You
> need at look at the queries you intend to run against the system and construct indexes
> which support them.
> 
>  - md
> 
> On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins <neil.tompkins@stripped>
> wrote:
> Maybe that was a bad example.  If the query was name = 'Red' what index should I
> create ?
> 
> Should I create a index of all columns used in each query or have a index on
> individual column ?
> 
> 
> On 6 Oct 2011, at 17:28, Michael Dykman <mdykman@stripped> wrote:
> 
>> For the first query, the obvious index on score will give you optimal results.
>> 
>> The second query is founded on this phrase: "Like '%Red%' " and no index will
> help you there.  This is an anti-pattern, I am afraid.  The only way your database can
> satisfy that expression is to test each and every record in the that database (the test
> itself being expensive as infix finding is iterative).  Perhaps you should consider this
> approach instead:
>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
>> 
>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil
> <neil.tompkins@stripped> wrote:
>> Hi,
>> 
>> Can anyone help and offer some advice with regards MySQL indexes.  Basically
>> we have a number of different tables all of which have the obviously primary
>> keys.  We then have some queries using JOIN statements that run slowly than
>> we wanted.  How many indexes are recommended per table ?  For example should
>> I have a index on all fields that will be used in a WHERE statement ?
>>  Should the indexes be created with multiple fields ?  A example  of two
>> basic queries
>> 
>> SELECT auto_id, name, score
>> FROM test_table
>> WHERE score > 10
>> ORDER BY score DESC
>> 
>> 
>> SELECT auto_id, name, score
>> FROM test_table
>> WHERE score > 10
>> AND name Like '%Red%'
>> ORDER BY score DESC
>> 
>> How many indexes should be created for these two queries ?
>> 
>> Thanks,
>> Neil
>> 
>> 
>> 
>> -- 
>>  - michael dykman
>>  - mdykman@stripped
>> 
>>  May the Source be with you.
> 
> 
> 
> -- 
>  - michael dykman
>  - mdykman@stripped
> 
>  May the Source be with you.
> 
> 
> 
> 
> -- 
>  - michael dykman
>  - mdykman@stripped
> 
>  May the Source be with you.

Thread
MySQL IndexesTompkins Neil6 Oct
  • Re: MySQL IndexesMichael Dykman6 Oct
    • Re: MySQL IndexesNeil Tompkins6 Oct
      • Re: MySQL IndexesMichael Dykman7 Oct
        • Re: MySQL IndexesNuno Tavares7 Oct
          • Re: MySQL IndexesBrandon Phelps7 Oct
            • Re: MySQL IndexesMichael Dykman7 Oct
              • Re: MySQL IndexesReindl Harald7 Oct
                • Re: MySQL IndexesMichael Dykman7 Oct
                  • Re: MySQL IndexesNeil Tompkins7 Oct
                    • Re: MySQL IndexesMichael Dykman7 Oct
        • Re: MySQL IndexesTompkins Neil7 Oct
          • Re: MySQL IndexesMichael Dykman7 Oct
            • Re: MySQL IndexesNeil Tompkins7 Oct
              • Re: MySQL Indexesmos7 Oct