Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Re^4: Hash lookups, Database lookups, and Scalability

by davido (Archbishop)
on Oct 31, 2004 at 16:00 UTC ( #404141=note: print w/ replies, xml ) Need Help??


in reply to Re^3: Hash lookups, Database lookups, and Scalability
in thread Hash lookups, Database lookups, and Scalability

I see that a properly constructed index is going to be vital for optimum performance. Given the criteria of the dual crossreferenced lookups, how might I better construct the indices in the code I posted? I'm curious to see if the DB solution can be better optimized.


Dave


Comment on Re^4: Hash lookups, Database lookups, and Scalability
Re^5: Hash lookups, Database lookups, and Scalability
by mpeppler (Vicar) on Oct 31, 2004 at 16:19 UTC
    Indexes and query behavior is usually pretty tied to the way a particular database engine works - and I don't know SQLlite at all, so I can't really help you with specifics.

    However, your table schema is exceedingly simple, so you really only have two choices:

    create unique index left_ix on words(left)
    or
    create unique index left_ix on words(left, right)
    (and their opposites).

    The first form is more "correct" - you really only want the key in the index. The second form may give you slightly better performance, at the expense of allowing duplicate "left" words into the table as long as they point at a different "right" word, and slightly more work during inserts (index maintenance is a little more complicated).

    Personally I'd use the first form (index on "left", and a separate index on "right").

    Michael

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://404141]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (6)
As of 2014-12-22 08:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (112 votes), past polls