Which is faster?

I ran into the same problem about half a year ago. I currently have around 30 fields in one Table and nearly 2 million rows.
After searching Google endlessly and saw no definite right/wrong database structure I decided to put everything into one database and one table. I then Indexed everything and then saw a major speed increase for Queries when I set MySQL to place all of the Indexes into RAM. I currently have 4 Gigs of RAM on my Dell PowerEdge 2950 Server, Quad Core Processor and set 2 Gigs aside for Index Cache alone. As my Database grows I will just keep adding RAM until I run out of RAM space/size. Once I get to the next level I will think about clustering but that will take another couple years I suspect as I am currently adding about a million new rows a month. Good Luck on your final decision.

Thanks for your input, I really appreciate it very much.

Whenever i design a database for some apps i make sure everything goes where it logically belongs. I try to "normalize" the data into logical entities. In normalizing your data you order info in a way so that you have as less as possible redundancy, which will comfort your way of thinking about your database, the speed of your database, the maintainability of your database, the quality of the app you will build on it, etc, etc...
The ultimate not-normalized database is a database with one table. In the worst case scenario you have a field for every little piece of info and hardly ever you will find a fully filled record. The record has to service many types of info. In the second worst case scenario the record has 2 fields, 1 identifier and some blob to store the info .... <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=44151&image=1&table=forumreplies' class='bbc_emoticon' alt=':o' /> Lots of overhead and an abuse of a RELATIONAL database.
After normalizing my data i have a view on all the tables the db will hold, including the relations between them. Then i add a primary key to each table and define the foreign keys. Them keys define the relations between tables and a user will never ever be able to use them or even see them, it's the way info is tied to eachother.
Relations in my databases are mostly "one to many". One "Company" holds one or more "CompanyContacts", one "Order" holds one or more "OrderDetails", a "Company" has one or more records in "Order", etc.
There's huge amounts of docs on how to design a relational database.
Speed by index in a database becomes important as soon as the database holds more then a few records. Imagine a non indexed table where i search on SOMEFIELD_ID=376. Without any index the databaseserver has no other option then to browse through each record of sometable to see if the search condition evaluates true and since it doesnt know if this is a unique value it will continue doing this till the end of the table is reached. Imagine a search on a non indexed field in a table with 20 million records, this can take a minute and will speed up dramatically by adding an index on the searchfield. If the field is uniquely indexed the result will show up instantly.
Where you put your indexes is highly dependant on how you construct your queries, for many WHERE clausules in your sql you will benefit by putting an index. Understand that you will gain about twice the speed by putting an index on the SEXE field in a persondetailstable as it will devide your data into 2, probably equally populated, pieces, MALE and FEMALES. .... WHERE PERS_SEX=F and PERS_NAME="JOHNSON"
Not sure about mysql, but i thought primary keys were automatically indexed, as it IS an unique index, all others you have to define.
Putting indexes has cost on the other side, it's slower to write records. For both normalizing and indexing goes, don't overdo it, keep focus on the endresult and the user.
Memory usage correlates to speed, the slower a query runs, the more likely it lacks an index (or poor design), the more memory it uses.
It has become more text then i intended as i started writing this. Just to give you an idea on how i think on databasedesign.

WOW great - thanks for all the input. It is really helping me shape up my databases.
Thank you all, any more advice I gladly accept.
Kim

Kim,
When building my first app I had the same thoughts as you. I planned on eventually building many different applications for people and other companies and I wanted to do it the right way from the start.

So after a couple weeks of asking around and reading articles about it, i decided to bite the bullet and take the time to build the same application but using both approaches. Building a single table with 26 fields was (hands down) much much quicker initially than creating 8 different tables and setting up master detail relationships to link them all together. But I found that after testing these independently with a couple hundred thousand entries each, there was absolutely no question in my mind that creating many tables was the way to go. The searches were much quicker, adding and deleting records seemed many times more responsive, and being that your records are created and amended in stages using master detail relationships, the add and edit pages were much more organized and uncluttered.

Being only a couple years into building these apps and databases, I'm really not a pro by any means, but I have been setting my apps up with as many different tables as possible and it sure has paid off for me. I takes a bit longer and a bit more thought needs to be put into your application but I have never been disappointed with the performance of any of my applications, and some are pretty large...
Hope it helps!

Good Luck,

Vince

I do quite a bit of database design at work, nothing huge, but speed matters.

I always have an auto increment field for the index in every table.

All the cross table links are numeric, and I normalise the data down to level 3NF

This page may help

http://www.databasedev.co.uk/database_normalization_process.html
HTH