[SOLVED] MySQL Query

This topic is locked

[SOLVED] Â MySQL Query	3/1/2017 5:38:49 AM
PHPRunner General questions
S swanside author morning guys. Is it best to have have a like this SELECT job.Order_Date, job.AttendBy, job.End_Date, job.File_No, job.Job_No, job.CustomerId, job.CustomerId AS customerPK, job.CustomerId AS Customer_Name, job.CustomerEmail, job.Contract, job.Job_Description, job.VAT, job.Invoice_Printed, job.Invoice_Printing_Date, job.Invoice_Tax_Date, job.Payment_Received, job.Job_Finished, job.CustomerRef, job.Payment_Due_date, job.Payment_Date, job.Note, job.EngineerId, job.EngineerId AS PDAAllocation, job.EngineerId AS GasId, job.LocationId, job.LocationId AS locationPK, job.LocationId AS Order_Site_Address, job.LocationId AS SiteEmailAddress, job.LocationId AS SitePhoneNumber, job.JobTypeId, job.JobTypeId AS JobSheet, job.Exported, job.Logged_By, job.Logged_By AS ContactEmail, job.PhoneNumber, job.ResponseCatagories, job.CallerName, customer.Customer_Name AS CustomerName, sites.site_address AS SiteAddress, account.Engineer_Note AS Engineer, sites.SPost_Code FROM job INNER JOIN customer ON job.CustomerId = customer.CustomerId INNER JOIN sites ON job.LocationId = sites.LocationId INNER JOIN account ON job.EngineerId = account.EngineerId ORDER BY job.Job_No DESC Or make my job table bigger and write all the info in there in the first instance so its not looking across tables, so it would be like this SELECT job.Order_Date, job.AttendBy, job.End_Date, job.File_No, job.Job_No, job.CustomerId, job.customerPK, job.Customer_Name, job.CustomerEmail, job.Contract, job.Job_Description, job.VAT, job.Invoice_Printed, job.Invoice_Printing_Date, job.Invoice_Tax_Date, job.Payment_Received, job.Job_Finished, job.CustomerRef, job.Payment_Due_date, job.Payment_Date, job.Note, job.EngineerId, job.PDAAllocation, job.GasId, job.LocationId, job.locationPK, job.Order_Site_Address, job.SiteEmailAddress, job.SitePhoneNumber, job.JobTypeId, job.JobSheet, job.Exported, job.Logged_By, job.ContactEmail, job.PhoneNumber, job.ResponseCatagories, job.CallerName, job.Customer_Name AS CustomerName, job..site_address AS SiteAddress, job..Engineer_Note AS Engineer, job.SPost_Code FROM job ORDER BY job.Job_No DESC

Mark as Solved Mark as not solved

4 replies


	90288 3/1/2017
	morning guys. Is it best to have have a like this SELECT job.Order_Date, job.AttendBy, job.End_Date, job.File_No, job.Job_No, job.CustomerId, job.CustomerId AS customerPK, job.CustomerId AS Customer_Name, job.CustomerEmail, job.Contract, job.Job_Description, job.VAT, job.Invoice_Printed, job.Invoice_Printing_Date, job.Invoice_Tax_Date, job.Payment_Received, job.Job_Finished, job.CustomerRef, job.Payment_Due_date, job.Payment_Date, job.Note, job.EngineerId, job.EngineerId AS PDAAllocation, job.EngineerId AS GasId, job.LocationId, job.LocationId AS locationPK, job.LocationId AS Order_Site_Address, job.LocationId AS SiteEmailAddress, job.LocationId AS SitePhoneNumber, job.JobTypeId, job.JobTypeId AS JobSheet, job.Exported, job.Logged_By, job.Logged_By AS ContactEmail, job.PhoneNumber, job.ResponseCatagories, job.CallerName, customer.Customer_Name AS CustomerName, sites.site_address AS SiteAddress, account.Engineer_Note AS Engineer, sites.SPost_Code FROM job INNER JOIN customer ON job.CustomerId = customer.CustomerId INNER JOIN sites ON job.LocationId = sites.LocationId INNER JOIN account ON job.EngineerId = account.EngineerId ORDER BY job.Job_No DESC Or make my job table bigger and write all the info in there in the first instance so its not looking across tables, so it would be like this SELECT job.Order_Date, job.AttendBy, job.End_Date, job.File_No, job.Job_No, job.CustomerId, job.customerPK, job.Customer_Name, job.CustomerEmail, job.Contract, job.Job_Description, job.VAT, job.Invoice_Printed, job.Invoice_Printing_Date, job.Invoice_Tax_Date, job.Payment_Received, job.Job_Finished, job.CustomerRef, job.Payment_Due_date, job.Payment_Date, job.Note, job.EngineerId, job.PDAAllocation, job.GasId, job.LocationId, job.locationPK, job.Order_Site_Address, job.SiteEmailAddress, job.SitePhoneNumber, job.JobTypeId, job.JobSheet, job.Exported, job.Logged_By, job.ContactEmail, job.PhoneNumber, job.ResponseCatagories, job.CallerName, job.Customer_Name AS CustomerName, job..site_address AS SiteAddress, job..Engineer_Note AS Engineer, job.SPost_Code FROM job ORDER BY job.Job_No DESC It depends to what you need to do with your data, but for me I think better to create different tables and play with any query as mush asyou need instead of having all fields in one table


	Sergey Kornilov admin 3/1/2017
	It really depends. Without specifics it sounds like "what car is better" kind of question. Depends on what kind of data you have, how it is related to other data, what you need to do with it. In general, if data in those tables is related one-to-one, splitting it into several tables brings nothing but pain.


S	swanside author 3/1/2017
	Thanks guys The main page that gets accessed is the jobs page and also searched upon. So I feel that to have the data all in one table that displays everything without going across tables, the quicker it would be. From the jobs page the client has a search for customer name, so they need to display all the data from the jobs upon what customer name is in the customer table. If the customer name was already in the jobs table, I feel it would be a lot quicker. I an going to copy the jobs table and then add the other fields and using firefox, see which one brings back the information quicker. But once I have completed my test I will post the firefox results and see what the difference is. Thank you As for car, I go for Audi <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=81496&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />


S	swanside author 3/3/2017
	Well, The long query takes 6.51s to load when using firefox and the new query takes 1.16s to load if I put all the fields in the one table, so its not cross searching between tables <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=81508&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />

4 replies

Mark as Solved Reply