I guess my view has been changed because I use MySQL (MariaDB) on the
backend now (for years) and I believe I learned that it won't optimize
with multiple indexes...it only chooses one. So if I had 3 separate
indexes, it'd only use one of them, whereas if I had all 3 fields in the
index, my EXPLAIN showed that I'd get a better result (if I were
searching for all 3 fields). You've used MySQL and even been certified
as I recall in it, Ted. Am I wrong?
Thanks,
--Michael
On 2014-07-17 07:31, Ted Roche wrote:
The compound index will only work on the one situation where you are
processing field1+field2+field3 ("ORDER BY field1+field2+field3") while
using individual indexes lets Rushmore use the for more operations
(like
WHERE field1=this and Field2=that). Rushmore is primarily focused on
the
WHERE clause, since you lose speed reading records you don't have to.
In nearly all cases, individual, atomic indexes, one field each, are
better
than multiple.
However, in optimizing a single statement, the key is to ensure the
LEFT
SIDE of the WHERE expressions are EXACTLY the same as the expression
used
to create the index, such as:
WHERE STR(iClientID,8) = "12345678"
will only work if you have an index created with that exact same
expression,
INDEX on STR(iClientID,8) TAG AReallyDumbIndex
On Wed, Jul 16, 2014 at 6:05 PM, Jeff Johnson <[email protected]> wrote:
Michael: I was going to say that but I didn't have the guts. ;^)
On 7/16/2014 2:41 PM, [email protected]
wrote:
I'm gonna make a wager and say "NO", and I know that's a large risky
wager seeing how it's Ted's comment (and we all know he's an ace!).
Why not the compound index as you (Jeff) described? I would have
said
the same thing.
--Mike
On 2014-07-16 17:23, Jeff Johnson wrote:
Ted: So these indexes are created so that when you run that select
statement it will put them in order?
Interesting.
On 7/16/2014 1:56 PM, Ted Roche wrote:
Sytze:
I've been surprised to find everyone works with a "large amount of
data"
but for some of us, that's thirty thousand records and for others
it is
thirty million.
INDEX ON pcode TAG pcode
INDEX ON pcode2 TAG pcode2
INDEX ON pcode3 TAG pcode3
INDEX ON DELETED() TAG DELETED
On Wed, Jul 16, 2014 at 4:40 PM, Sytze de Boer
<[email protected]>
wrote:
After yesterday, with heart in mouth, I ask the following
I have a table with large amount of data
It contains Master contract, sub contract, sub-sub contract
pcode N(5)
pcode2 N(7)
pcode3 N(9)
Example
3770
3770, 377001
3770, 377001, 37700101
3770, 377001, 37700102
3770, 377002
3770, 377002, 37700201
3770, 377002, 37700202
When I do a select statement, I seem to get them in correct order
with
select * from contract order by pcode,pcode2,pcode3 etc
But if I want to create an index, I lose my head.
Any pointers?
--
Kind regards,
Sytze de Boer
Kiss Software
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
