MySQL Forums
Forum List  »  Performance

Representing an array for best performance
Posted by: Margaret MacDonald
Date: November 25, 2004 10:12AM

I'm not a database person, so the answer to this might be obvious to everyone but me!

For the best performance, how should I represent an array for lookup?

I'm implementing interlocking trees, in which some leaf C can be the child both of branch A and branch B. Right now I'm encoding the relationship in a shared token: C has tokens for both A and B as substrings in a varchar field. So my SELECT would be LIKE "%sometoken%". But the other day in the documentation I ran across a note that says a LIKE with a leading % doesn't use indexing, it uses a brute-force search. That made perfect sense when I came to think about it (which til that point for some reason I hadn't :-( ) But that leaves me in the dark about how best to represent the relationships for the fastest lookup.

I've thought of creating a relationship table (I'm sure there's a technical name for it) that would have one record for the AC relationship and one record for the BC relationship. But then, to select out all the kids of B, I'd have to read up all the BC relation records, build an IN list from them, and then do the read I actually want. Which seems like it would also be very expensive.

Is there a better way? Thanks in advance for any insights!

Options: ReplyQuote

Written By
Representing an array for best performance
November 25, 2004 10:12AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.