Connection/Server Time-Multiple Tables

Not sure where all of the old threads went, but basically I'll continue mine here.

I am going to use a database to set up the structure of my main pages on my website. So I will pull links and article descriptions from a database on my main page and then on my subject pages. My question was whether is best on the server to have all of the articles in one table or to have them in separate tables by subject type.

I will never have reason to pull the 10 most recent articles overall. Instead, on the front page, I will say pull the most recent article from 5 subjects. So on the front page the question is, is it quicker to do 5 queries from 5 smaller tables (subject tables) or 5 queries from one larger table (Overall table)?

And then I will have individual subject pages. On these pages I will pull say the 10 most recent articles for that subject. If I have tables for each subject I will be pulling from a smaller table. So is it quicker to pull 10 articles from a smaller tablee or to pull 10 articles from a larger table that would contain every article from all of the subjects?

Hopefully this makes since, someone replied earlier before I had this much detail that everything should be in one table but I'm not sure if they were thinking about server speed.

Thanks

I posted a reply before it all died, and I stick by the one table argument. :-)

Basically, you'll see no difference in speed between having it all in one table and having it in separate tables, but you will end up with a less maintainable solution with the separate tables. If speed is a real problem, you'll want to make sure you do a couple of things:

(1) Queries should be on simple values (preferably integers) when those queries are going to have to look through a ton of information (basically, don't do large string comparisons on long strings if you want to have a speedy lookup).
(2) You should add indexes to the columns in the table that are going to be used in lookups. In your case, you'll want to put an index on the 'subject' column for quick access when looking up by subject, and another on the 'time' column for quick access when getting, say, the last 10 items.

Really, unless you're going to be storing thousands upon thousands upon thousands of records (I mean one HELL of a lot of records) the cost of retrieval of data is going to be insignificant compared to network latency and database connect times (on which note, use persistent database connections if you can, as that'll whack speed up a lot). There is some documentation about improving the speed of your DB at the MySQL web page (http://www.mysql.com/) which you may want to look at if you're really concerned about this, but I think your worries are probably unfounded

You say:
(1) Queries should be on simple values (preferably integers) when those queries are going to have to look through a ton of information (basically, don't do large string comparisons on long strings if you want to have a speedy lookup).

Are dates considerend fairly simple values? I was thinking I would just store everything by date, but I guess I could have an integer that corresponds to everything in the database so the most recent record would be 1, etc and then have a similar index by subject and query by this number instead of date. But if I did this I would have to update these values everytime I added a record.

Yeah, a date is a simple value. It's stored internally as an integer (a big one, but an integer nonetheless). I should have been a little clearer with what you don't want to do:
You don't want to perform a string comparison on a lot of rows, especially if the string you're comparing to in the table is long and you use a lot of wildcards in your search value. This would be slllooow. It could be sped up with judicious use of indexes, but it's a situation best avoided where possible.

So, given a table def like:
CREATE TABLE large (
id INT PRIMARY KEY auto_increment,
longstr VARCHAR(32000) );

You want to be doing queries like:
SELECT * FROM large WHERE id = <someid>
where possible, rather than something like:
SELECT * FROM large WHERE longstr LIKE '%abc%'