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.
