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 <mdyk...@gmail.com> 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.tompk...@googlemail.com> > 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 <mdyk...@gmail.com> 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.tompk...@googlemail.com> > 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 <mdyk...@gmail.com> 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.tompk...@googlemail.com> 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 >> - mdyk...@gmail.com >> >> May the Source be with you. > > > > -- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you. > > > > > -- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you.
