multiple table join vs single table q's

[Anon]

$query="SELECT Company.CompanyID, Company.Name, Company.Street, Suburb.Name AS s_name, Company.Priority, Company.Telephone, Company.Email, Company.Web, Company.Map, Company.Editorial, Company.BookIt, OrderTable.OrderID FROM Category, Company, CompanyAmenity, SubAmenity, Subcategory, Suburb, Contact, OrderTable WHERE Company.CompanyID = CompanyAmenity.CompanyID AND Company.SuburbID = Suburb.SuburbID AND Suburb.CityID = $cityID AND CompanyAmenity.SubAmenityID = SubAmenity.SubAmenityID AND SubAmenity.SubcategoryID = Subcategory.SubcategoryID AND Subcategory.SubcategoryID = $subID AND Company.ActStatus = 1 and Company.CompanyID = Contact.CompanyID AND Contact.ContactID =OrderTable.ContactID GROUP BY Company.Name ASC ORDER BY Priority, Company.Name limit $set, $limit";
$result = mysql_query ($query,$db_link_id);
$reult_num=mysql_num_rows($result);
if($reult_num !="0"){
while($myrow = mysql_fetch_array($result)){
$company_id=$myrow[CompanyID];
$company = $myrow[Name];
$address = $myrow[Street];
$suburb = $myrow[s_name];
$telephone = $myrow[Telephone];
$email = $myrow[Email];
$web = $myrow[Web];
$map = $myrow[Map];
$editorial=$myrow[Editorial];
$book=$myrow[BookIt];
$order_id=$myrow[OrderID];

This is the code i am currently using. A large multy table query. I really need all of that query in their except the OrderID bit. Is it quicker to get the OrderID via a second seperate query or just leave it in the first multy table join. I have resently struc some major problems with another cross table join that i fixed by adding OrderID into this main query. But this table will soon have 150-180k company records in it and the OrderTable about the same.Is this likely to create a alot slower query. I am really apealing for someone who has alot of DBA experaince or something to help me. I have only been doing this for 6mnth on a small DB and have never encountered these problems. Need some guidence andsuggestions
thanks

[sarahk]

Hi Bryce

I'd be interested to know more about the work you're doing. Not often you get a kiwi asking a question.

It's a cool question and I think the answer is to use one of the tools like this:

Description:
compute the time elapsed for a given process (i.e. showing a web page into
a browser)

URL: http://phpclasses.upperdesign.com/browse.html/package/412

I'd be inclined to run a script to load the data into my tables (pain but worth it) - you'll probably end up with names like "Smith 123" and give each company the type of data loads they'd have.

Why do you have the group by? Looks like an unnecessary load on your query.

[[deleted]]

You query is very strange.

You use GROUP BY to get results 'per companyname' but you also select the orderid. I assume your database can hold more than one order per company, so which order should this query display? The orderid does not belong in this query, or you need to change the query so it lists per orderid instead of per company.

(MySQL has extremely bad 'GROUP BY' policies. In real life, it is not possible to select fields that are not in the 'GROUP BY' clause, because there may be several different values for those fields. One company, many orders. If you 'GROUP BY' on company, you will get only one result for evey unique company name. But every unique comapny name can have a hunderd orders, so you cannot select the orderid without grouping by orderid too.)