This thread has sparked my interest. What is the difference between an index on
(field_a, field_b) and an index on (field_b, field_a)?
On 10/06/2011 07:43 PM, Nuno Tavares wrote:
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
