General Database Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Saethyr
Forum Contributor
Posts: 182
Joined: Thu Sep 25, 2003 9:21 am
Location: Wichita, Kansas USA
Contact:

General Database Question

Post by Saethyr »

Sup!

Say I have 262,008 records, what would be the quickest way to query this from the web? Should I break this in to like 10+ tables and query all the tables or just use one table? I know regardless this is going to take some time to query.

I was thinking

tbl1-tbl15?

Not real sure how to do this.


Saethyr
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Do you want to show all records or just some of them when displaying the data on your pages?

I mean, if you have 262.008 ipnumbers you want to display (dunno why but as example) one table is sufficient. But if you have 262.008 usernames, passwords, messages, comments, private messages and so on (like this very board we are using) you would likely want to use multible tables and join them upon retrieving the data...

Can you give us some more background information?
User avatar
Saethyr
Forum Contributor
Posts: 182
Joined: Thu Sep 25, 2003 9:21 am
Location: Wichita, Kansas USA
Contact:

Post by Saethyr »

Jam, sure thing...


The data is set in 3 colums

areacode
prefix
company

i.e.
areacode prefix company
816 300 Birch Telecommunications

This will be a script that chooses the company based on input of areacode AND prefix. So in esscence it is going to have to search all the records to find the company corresponding with 816 and 300.

another small example that might make more sense

Code: Select all

$query = "SELECT company from sometable WHERE areacode = '$areacode' AND prefix = '$prefix' LIMIT 1"

Saethyr
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

I think that a single table holding those three fields of data would be enough.
I'm not sure it's a wise idea trying to break that down into any more as it would require you to insert something that relates the different tables, for example id's.

-
Sidenote edit:
LIMIT 1 might be bad to use, IF you happen to have two companies at the same prefix and areacode (just might). At least have in mind that you might need some random function in the future...
Last edited by JAM on Thu Dec 18, 2003 2:37 pm, edited 1 time in total.
User avatar
Saethyr
Forum Contributor
Posts: 182
Joined: Thu Sep 25, 2003 9:21 am
Location: Wichita, Kansas USA
Contact:

Post by Saethyr »

good point, but with that many records wont it take like a month to retrieve 1 company?


Saethyr
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

one query against the table with 100 records is usually faster then 10 queries against the 10 tables with 10 records in each.
Last edited by Weirdan on Thu Dec 18, 2003 2:41 pm, edited 1 time in total.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Would not think so. A simple query like that is extremely speedy. And have inte mind that joining tables uses more server resources than a 'simple' select clause.
User avatar
Saethyr
Forum Contributor
Posts: 182
Joined: Thu Sep 25, 2003 9:21 am
Location: Wichita, Kansas USA
Contact:

Post by Saethyr »

Danke guys, I will just write it to a DB and see what happens., it may not even be a fesible solution to do it this way.


Thanks Again,

Saethyr
Post Reply