Re: Ultra fast db access & searching?

IchBin wrote:
Dyreatnews@xxxxxxx wrote: 
"Aerodyne" <aerodynex@xxxxxxxxx> writes:

Thanks IchBin .... but I'm sure even with HSQLDB searching through
millions of unique user names can be optimize in some way or another.
What are the best solutions for this task or do most just leave the
database do it with out a good design.

Hotmail has 150+ million users ... yet they get back to you very
quickly as if the user name is already in use & several suggestions!

I'm just looking for good database design if any or tricks for fast
searching... ie instead of putting the complete city name in a field
(LosAngeles) you just use LA, that way you only have to search for two
characters rather than all 10 for equality or like wise for all other
cities ... just a 3 letter acronym.


You are on to something here. I believe it is called hashing :)

Yes it is called hashing but the Database does all of this thru keys and indexes under the covers for. Defining these relationship's using PK, FK and indexes correctly makes queries run so fast.

IMHO - It still depends on the relationship of those keys and indexes to be defined correctly and the knowledge of correct SQL statement definition to optimized the use of the fore mentioned keys an indexes. Also to normalize you database the best you can.

Looking for a state can be optimized by defining it as a unique index or primary key. Defined as a FK when if referenced by other tables. You do not want to do any hashing. This is why you have a DBMS. You may want to google for "Database normalization techniques" also.

Here are some good links for review..

http://en.wikipedia.org/wiki/Hash_table
http://www.datamodel.org/index.html
http://en.wikipedia.org/wiki/Database_normalization
http://www.datamodel.org/NormalizationRules.html

Forgot to saying something about lookup tables or maintenance tables.

Good Example would be a State table.

http://www.datamodel.org/DataModelLookup.html
http://www.datamodel.org/DataModelKeys.html
--


Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.servebeer.com/JHackerAppManager
__________________________________________________________________________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor,  Regular Guy (1952-)
.

Relevant Pages

  • Re: Ultra fast db access & searching?
    ... millions of unique user names can be optimize in some way or another. ... What are the best solutions for this task or do most just leave the database do it with out a good design. ... Defining these relationship's using PK, ... IMHO - It still depends on the relationship of those keys and indexes to ...
    (comp.lang.java.databases)
  • Re: Key attributes with list values was Re: What are the differences ...KEY
    ... Jane Harper is married. ... And a constraint that states that single people cannot become divorced. ... database, or users, for that matter, to distinguish between them. ... That's the whole point of keys. ...
    (comp.databases.theory)
  • Re: Pin generation algorithm question
    ... the keys would be a big ... Suppose that we have a database that contains all valid numbers, ... load among several servers that all need access to this database. ... So the only real problem is which systems are accessing this crypto box. ...
    (sci.crypt)
  • Re: Database design, Keys and some other things
    ... >> Or 'the database has no opinion as what Donald Trump's e-mail address might ... some keys can be wrong or a data can ... Meaning is not related to just one number. ... > is concerned a VIN is not a surrogate key, ...
    (comp.databases.theory)
  • Re: Key attributes with list values was Re: What are the differences ...KEY
    ... database, or users, for that matter, to distinguish between them. ... That's the whole point of keys. ... But that is true of any constraint. ... keys can change, then either updates must be singular, that is, must affect ...
    (comp.databases.theory)