I am not an expert at sql, but am trying to work towards one query for my lists, as opposed to making calls within a loop from a more basic query (i know, inefficient). I could live with the inefficiency to get this done, but sorting becomes tricky of course, so want to do in one query.

I have tables: Accounts, Devices, DeviceLogs, AccountHistory. Accounts has key AccountID and devices has DeviceID. There is a AccountID field in Devices to associate devices to accounts (so one to many Accounts>Devices). The device logs has DeviceID (one to many Devices>DeviceLogs). The AccountHistory has both AccountID and DeviceID so it can track history for both Account and Device activity (maybe bad idea).

I have this working perfectly...

SELECT d.* , a.Name as acctName, MAX( dl.CallDateTime ) AS maxdate, COUNT(bbID) as callcount
FROM Devices d
LEFT JOIN DeviceLogs dl ON d.DeviceID = dl.DeviceID
LEFT JOIN Accounts a ON d.AccountID = a.AccountID
WHERE d.AccountID = '14'
GROUP BY d.DeviceID

Now they want info from the AccountHistory, so I want to get the MAX(LogTime) for a particular DeviceID. It allows me to join using this
LEFT JOIN AccountHistory ah ON d.AccountID = ah.AccountID
adding this to the select: MAX(ah.LogTime) as lastLog

But I need the last DeviceID entry, not the last AccountID entry. So when I try this, it times out (assuming there's too much to deal with for the query)...maybe because there are many more records when looking for the DeviceID as opposed to the AccountID.

LEFT JOIN AccountHistory ah ON d.DeviceID = ah.DeviceID
times out...so my full query that is timing out is...

select d.* , a.Name as acctName, MAX( dl.CallDateTime ) AS maxdate, COUNT(dl.bbID) as callcount, MAX(ah.LogTime) as lastLog from Devices d LEFT JOIN DeviceLogs dl ON d.DeviceID = dl.DeviceID LEFT JOIN Accounts a ON d.AccountID = a.AccountID LEFT JOIN AccountHistory ah ON d.DeviceID = ah.DeviceID where d.AccountID = '14' group by d.DeviceID

I am guess maybe a subquery in here to reduce the items in the AccountHistory?
Hope this isn't too confusing, and I haven't left out any necessary info...

Any ideas? I was doing great til this last table...

hi there...

just an idea, but how about joining only devices and accounthistory to see if it timeout???
maybe there is too much data on AccountHistory?? can you use the Query analizer to see if you will do it better with an index???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========

Thanks for the reply!

Just joining the Devices and AccountHistory gave me acceptable query times. I have indexes on any AccountID or DeviceID fields.

My particular test call, returns two devices, one with 120 accounthist records, and the other with 727 records.

ok.. witch language are you using??

maybe the dataset (or recordset) can be configurable to have more timeout time??

also did you try the query analyzer???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========

I have to apologize, I put in the wrong mysql version in the subject line of this thread!
mysql 4.1.10 and php 5

I will try and find a query analyser.

Seems to me though that it was a flaw in the logic of the statement, and if it's really this big of a process, I should rethink and find a different way to get this information. I can loop through the records and make calls to the db to get this info, and then wrap the result in js so they can sort client-side. But with sql, I thought there was pretty well always a way to get the info in one call...so here I am.