I appreciate everyone's responces. It's really great to be on this list :) I'll try to answer some your questions. Donal McMullan wrote: > but what are you running this on? Is the DB server only serving the database? The machine have not been set up yet. We want to test the whole idea before spending big bucks on hardware. Our IT guy is investigating how to set up a machine tha would have as much RAM as possible with the motherboards we already have. I am going to produce a smaller database specifically for testing, and then pray that the results scale. The server will be dedicated to serving the database over web interface, so it will likely run MySQL + Apache. Neil Davis wrote: >Can we see the code and queries in question? It may be a better solution to >clean up the queries(and code), perhaps change multiple queries into one >query ... >Indexing the tables would undoubtedly help as well. If they already are >indexed, when is the last time they were re-indexed and cleaned up?(this >needs to be part of your maintenance plan, preferably automated) The actual code is rather large, so I won't post it here. But I can explain the general idea. There are 4 databases: a, b, c, d. Each database has a set of tables: table a_entries (autoincrement autoid, char id, char field1, text field2, ...) table a_dimension1 (autoincrement autoid, char entry_id, char field1, text field2, ...) table a_dimension2 (autoincrement autoid, char entry_id, char field1, text field2, ...) ....... Each database contains a set of objects, or entries. Each entry has a unique id. There is one row in the "entries" table for each entry. There can be more than one row per entry in each "dimension" table. For example, a person's name would be listed in table person_entries, while his/her bank accounts would be listed in table person_accounts and cross-referenced back to him like so: person_entry.id = person_accounts.entry_id. The entries from the 4 databases are cross-referenced to each other. The cross-references are listed in special tables: table xref_a_b(char a_id, char b_id) The goal is to search all 4 databases and retrieve entries in database "d" either directly or using cross-references from the other databases. For example, if an entry in database "a" has been found, use the cross-reference table to retrieve the corresponding entry in "d". So, each table needs to be searched, and then the cross-reference tables need to be searched as well. While some parts of my code can undoubtedly be optimized, I can't possibly get away from multiple queries and/or joins. Besides, the program being large, it's not too easy to tweak it without introducing bugs. So we thought that throwing some hardware at it might just solve the problem without having to rewrite things. As for indexes, most of my data fields are text, so I use a derivative of perl module DBIx::TextIndex to search them. All "id" fields are indexed, of course; and the entire database is re-built on a weekly basis. Yury "Yury V. Bukhman" wrote: > Hi! > > Background: > I have a moderately-sized (0.9G) database which serves as a backend to a > search engine. The search engine is kind of slow, because it has to run > multiple database queries for each user request (don't ask me why). In > order to speed things up, I am thinking of copying the entire database > into RAM. The users do not update it, they only search, so if the > server goes down, I can just copy the database from hard drive into RAM > again on startup. > > Question: > what is the best way to put an entire database into RAM? I thought of > using HEAP tables, but they seem to have some limitations, e.g. not > allowing text column type. Is it possible to just copy an entire data > directory into RAM? Are there operating systems that allow this? I > currently run Linux, but could switch easily to another UNIX-like > system. Have anyone done something like this? > > Any RTFMs or other pointers to literature will be appreciated. > > Cheers! > > Yury > > ------------------------------------------------------------------------ > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
