[thelist] database structure for speedier queries

Nan Harbison nan at nanharbison.com
Sat Dec 1 06:23:23 CST 2007


Ken, Matt and Joshua,

This is a MySQL db, hosted at Godaddy. I don't imagine we will ever have
millions of records. I am going on the assumption that there could be at
best be a few hundred thousand. We have limited RAM and Godaddy can't tell
me how much, I suspect that is because it is being shared with other
websites on the shared hosting. We can easily upgrade to premium hosting,
with fewer websites on the server.

I know this breaks normalization rules. But I thought it might better. It
sounds like adding the archived field and setting it to null or not null is
the best solution. It would make it easier to query for both current and
archived projects. The queries are simple, but could be frequent. I won't
figure out customer behavior until we get this site up and runnning.

Thanks for all your help!
Nan

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Ken Schaefer
Sent: Saturday, December 01, 2007 1:13 AM
To: thelist at lists.evolt.org
Subject: Re: [thelist] database structure for speedier queries

I would have to ask how "big" is "very long".

Most modern DBMS are quite capable of searching through tables with millions
(or tens of millions) of records given enough RAM, appropriate indexes and
good disk I/O (multiple spindles as required). Is your table likely to have
more than this?

The downside to having a separate table is that you break normalisation
rules, and then querying may become an issue (e.g. to present a consolidated
historical view you need to query two separate tables). Of course, only you
can say whether this is more efficient or not (it really depends on what
types of queries you are going to run, and how frequently).

Cheers
Ken

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Nan Harbison
Sent: Saturday, 1 December 2007 5:26 AM
To: thelist at lists.evolt.org
Subject: [thelist] database structure for speedier queries

Hi All,

I have a table of project data (id, type, contact person, description, etc)
which could get very long. When a project is finished, the user has the
ability to archive the project.
I see two ways to do this:

1. create a duplicate table with all the same fields  ('archived_projects'
or something) and move the information over to that table.

2.  have a field in the 'projects'  table to mark it as archived

It seems to me that the first way would be more efficient because when it is
searching for projects in progress, it checks a shorter list of projects.

Do you agree with this? I don't know much about efficient design.

TIA
Nan

-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester and archives
of thelist go to: http://lists.evolt.org Workers of the Web, evolt ! 




More information about the thelist mailing list