[MYSQL] Databases - best table structure?

Conrad11

Conrad11

Conrad11

Soldato
Joined
12 Jun 2005
Posts
5,361
Hi there,

I want some of your opinions on what would be the best file structure in terms of speed of the site in this case.

Basically its an articles site and the articles can either been shown, archived, waiting to be approved or in the bin.

Now the objective is to make it as fast as possible for the users that want to read the articles, so the articles that are shown and the archived articles which can be searched.

Now the shown articles (or links to) are all on the first page so are queried for straight away.

The user can then click a link and he is taken to a search where he can search through both the shown articles and the archived ones.

The articles waiting for approval and the ones in the bin arent seen by the public.

Now in terms of speed of the querying for the shown articles and the archived articles, in the situation above, what would be the best table structure.

For instance, my guess would be:

2 tables

table 1: shown articles and archived ones

table 2: recyling bin, and articles waiting to be approved.

Whats your oppnion?

Thanks...
 
Hi there,

So have it all in one table.

I have read through the ENUM bit about 5 or 6 times now and i still don't fullly understand.

Whats the differnce betwen an eNUM field and a field having either the numbers 1,2,3,4 in it to represent the four states the articles can be in? or is the ENUM field type not used for that?

Thanks...
 
Conrad11 said:
Whats the differnce betwen an eNUM field and a field having either the numbers 1,2,3,4 in it to represent the four states the articles can be in? or is the ENUM field type not used for that?
Click to expand...

No different really, apart from the fact it's a hell of a lot more readable if you're talking in terms of words rather than integers. Use integers if you feel more comfortable, but either way you're only going to need the one table.
 
Hi there,

I just thought, that if its all in the same talbe then the website will have to query much more than if it is split.

Surely querying much more takes a lot more time?

Thanks
 
Conrad11 said:
Hi there,

I just thought, that if its all in the same talbe then the website will have to query much more than if it is split.

Surely querying much more takes a lot more time?

Thanks
Click to expand...

You wouldn't need to use anymore queries. You'd just do something like:

Code: 
SELECT * FROM `article` WHERE `type` = "archived"

Although, I think I'm misunderstanding what you're saying. Anyway, MySQL can deal with oodles of data so I very much doubt you're going to have any issues with performance.
 
sorry, what i meant was, there will be a lot more records and thus there query will have to search through a lot more. Surely that would slow the site down?
 
Conrad11 said:
sorry, what i meant was, there will be a lot more records and thus there query will have to search through a lot more. Surely that would slow the site down?
Click to expand...

The searching algorithms in MySQL are going to be pretty sophisticated, unless you're dealing with a lot (and I mean a lot) of data then you're unlikely to get any real performance increase by doing what you're proposing.
 
Pine said:
The searching algorithms in MySQL are going to be pretty sophisticated, unless you're dealing with a lot (and I mean a lot) of data then you're unlikely to get any real performance increase by doing what you're proposing.
Click to expand...

Splitting it unnecessarily will in fact do the opposite and slow things down.
 
Indeed. At the end of the day, you're still searching the same amount of data. By splitting it into separate tables, all you're doing is adding overhead, due to the extra queries that have to be performed.
 
For 90% of websites you can structure the database how you like and you'll never be able to tell any performance differences.

I recently fixed up one of our client's databases....quite a busy site with 150Mb tables and no indexes :eek:
 
Hi there,

I just thought, because this is a redesign of a highly visited site (i think), 300k visits + per month, peaked at around 800k once, it would be best if there is less data, as the articles would be a could be a good 1000 words maybe a lot more i dunno, and remember the visitors would only ever be querying one table, and i thought you might be a significant increase in performance if there was less data.

Thanks for your responses peeps!
 
One table is less than two :)

What you need to do is lay down what you want to do, then transform what you want to do to programming terms.

If all you need to change is one property of an item then translates to a different value in the respective field of that item's record :)
 
Hi there,

What i am trying to say is, I dont care about the admin side and how fast that is........The USER will ONLY EVER SEARCH ONE TABLE.... even with my suggestion of 2 tables.

So im saying, that surely there would be a big increase in speed for the user using the site if there is less data in the table, concidering there can be 1000 words in just one field?


Thanks...
 
Last edited:
Conrad11 said:
Hi there,

What i am trying to say is, I dont care about the admin side and how fast that is........The USER will ONLY EVER SEARCH ONE TABLE.... even with my suggestion of 2 tables.

So im saying, that surely there would be a big increase in speed for the user using the site if there is less data in the table, concidering there can be 1000 words in just one field?


Thanks...
Click to expand...


Ooh, are we doing the big letter thing?

READ THE LINK I POSTED ABOUT AT WHAT TIMES MYSQL DOES FULL TABLE SCANS....
 
You must log in or register to reply here.
Back
Top Bottom