List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:October 7 2011 8:26am
Subject:Re: MySQL Indexes
View as plain text  
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>
>> 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>
>> 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>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