Re: Ultra fast db access & searching?

Aerodyne wrote: 
Hi all,

Could some one help me out with this Q?

If I designed a db (MySQL or PostgreSQL) & it contained more than 5+
million users with all unique user names... how can I speed up db
access ...etc to search a new users name if it has already been taken?

All hints & tips welcome (plus good db books on this matter & some)...

The reason I ask is to know how sites like hotmail, gmail, ebay ...etc
all do it so quickly!  But I'd prefer to use either MySQL or
PostgreSQL.

I'd put an index on the username field and increase the size of the MySQL key cache. The key cache is a specific block of memory that is used to store indexes. If this is large enough, MySQL may be encouraged to store the entire index in memory, so it won't need to access the disk to use that index.

See also http://dev.mysql.com/doc/refman/5.0/en/myisam-key-cache.html, and especially about index cache preloading: http://dev.mysql.com/doc/refman/5.0/en/index-preloading.html

Anyway, that should put the index into system memory, and subsequent lookups (B-tree lookups on a data structure cached in memory) will be about as fast as you could hope for, given the technology choice.

I'm not sure if PostgreSQL has a similar tuning option. I'll leave that research to you. :)

Regards,
Bill K.
.

Relevant Pages

  • Re: MySQL crashes on amd64
    ... >>1) Exchanging memory on the machine did not work. ... This is obviously not a hardware issue. ... The errors are reproducable for MySQL 4.1.15 and MySQL 5.0.15 so ... It is possible that mysqld could use up to ...
    (freebsd-current)
  • Re: MySQL crashes on amd64
    ... >>1) Exchanging memory on the machine did not work. ... This is obviously not a hardware issue. ... The errors are reproducable for MySQL 4.1.15 and MySQL 5.0.15 so ... It is possible that mysqld could use up to ...
    (freebsd-questions)
  • Re: MySQL/PHP: Check data length before INSERT?
    ... >> MySQL should cut it for you, if it is too long to fit, but of course ... >> never know if there is a bug in MySQL. ... buffer overrun is where you allocate a block of memory of fixed size, ... in PHP, this is not anything that you as a user of PHP need to be concerned ...
    (comp.lang.php)
  • Re: Page Cache writeback too slow, SSD/noop scheduler/ext2
    ... When memory gets low this will result in very irregular performance drops. ... These are single purpose machines running MySQL. ... The wk_update function does not write enough dirty pages, ...
    (Linux-Kernel)
  • Re: MySQL / apache tuning
    ... > would some of you recommend for values to properly tune MySQL for this ... FreeBSD will aggressively consume memory for cache, ... If mySQL is the prime offender, the first optimization, I would look ... may involve shared memory tuning to achieve ideal results. ...
    (freebsd-isp)