Hi everyone,
I have Two tables namely Clients and Contacts. Clients contain details of companies and contacts hold the contact person information of those companies. The contact persons may be many and so there is possibility of many records and in some cases no contacts also. So, there are 4000 records in Clients and around 3500 records in Contacts table. To get both the information, I run the query as
select cl.*, co.FirstName, co.LastName from clients cl Left Join contacts co on (cl.CompanyId = co.CompanyId) group by cl.companyId order by cl.companyname
My problem is :
1. MySql takes 19 seconds to compile the query... Is there a possibility to improve the performance of this query?
2. The Join returns all the multiple records for a particular company. But I would like to have just one record - a distinct record for each company.... Could anyone help me? It is urgent....
> 1. MySql takes 19 seconds to compile the query... Is there a possibility to improve the performance of this query?
Do you have indexes defined? From the dump, it seems not.
For a normal SQL table form:
- I would assume CompanyID should be unique for the clients table,
- clients.CompanyID should really not have possible NULL values. Dunno about MySQL, but I know that if you can specify field as NOT NULL, it speeds up queries.
You could try these:
CREATE UNIQUE INDEX clientIDs ON clients(companyID);
CREATE INDEX companycontacts ON contacts(companyID);
This should speed up the above query a lot.
Also note, that if you really use numbers for companyID, it is a waste to store the field as varchar, and this can slow things down considerably too. Same goes for other ID fields: ContactID, UserID.
> 2. The Join returns all the multiple records for a particular company. But I would like to have just one record - a distinct record for each company.... Could anyone help me? It is urgent....
Sure, you can, you just have to specify - _which_ contact should be selected for the particular company, somehow.
Let's say you want that contact for the company, who has been modified most recently:
SELECT DISTINCT cl.companyName, co.FirstName, co.LastName
FROM clients cl LEFT JOIN contacts co ON (cl.CompanyId = co.CompanyId)
GROUP BY cl.companyId HAVING co.modifiedDate = max(co.modifiedDate) OR co.modifiedDate IS NULL
ORDER BY cl.companyname
Something like that...