Neil, whenever you see multiple fields you'd like to index, you should consider, at least:
* The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool "Index Analyzer" that may give you some hints, and I think it's maatkit that has a tool to run a "query log" to find good candidates - I've seen it somewhere, I believe.... Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: > 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> >> 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> >> 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>mdyk...@gmail.com >> >> May the Source be with you. >> >> > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org