quicker queries

mystrymaster

I have to extract data from 6db's and I have to select 19 fields per db.

My question is would it be better for my to have 6 different queries selected 1 row from each or have 1 big query selecting 1 row from all 6 db's.

I will be selecting the data in each row that corresponds to one ID if that helps at all.

laserlight

Well, if you're using 6 different DBs I dont think you can use a single query, even if they are on the same database server.

mystrymaster

sorry about that - 6 different tables from the same db.

It's early - sorry

laserlight

Oh, ok.

Well, I personally have never tested, but according to some other ppl on this messageboard, using a single query would be faster.
However, that was mentioned for a single table, so I'm not so sure.
If you are using different tables it probably involves a JOIN or something, I'm not sure.

mystrymaster

they all have a courseid field in common - other then that nothing.

would that matter?

ahundiak

Try both ways and let us know which is faster.

A single query will probably be faster but since the tables have different columns then I think the query will be a real pain to write. I think you will have to explicitly specify each column and then give it a common alias.

I have a little app with 19 tables so far. Being a lazy programer, I usually end up with one query per table and avoid all the joins and what nots. So far, the performance has been acceptable.

mystrymaster

I will try it both ways and reply here as a test - answer to come later

mystrymaster

alright started writing this query and I have an issue that I am not sure how to deal with.

In 4 of these tables they have fields that are named the same how would I refer the them in my php code to pull them out.

example

SELECT ID, hole, rating FROM holeblack, holeblue, holered, holeblue WHERE (holeblack.courseid = $GET['hole']) AND (holeblue.courseid = $GET['hole']) AND (holered.courseid = $GET['hole']) AND (holewhite.courseid = $GET['hole'])

so now I would I echo out holeblue rating?