Will Nordin wrote:
MySQL Ver 8.41 Distrib 5.0.27
Intel Xeon
3.60 GHz
2.0 G of RAM
Windows Server 2003
Service Pack 1
The following query will sometimes work and sometimes fail. When it
fails it kills the mysqld service and requires it to be restarted.
Sometimes if I add additional id's to the IN clause it will fail more
often.
select distinct(ct.truck_name), (SELECT OID FROM
release2_85.cust_positions cp where cp.asset_id = ct.OID order by
date_time desc LIMIT 1) as MAXPOSID,
(SELECT date_add(date_time, interval -360 minute) FROM
release2_85.cust_positions WHERE OID = MAXPOSID) as date_time,
(SELECT location1distance / 1000 FROM cust_positions WHERE OID =
MAXPOSID) as location1distance,
(SELECT location1 FROM release2_85.cust_positions WHERE OID = MAXPOSID)
as location1
FROM release2_85.cust_trucks ct
where (SELECT OID FROM release2_85.cust_positions cp where cp.asset_id =
ct.OID order by date_time desc LIMIT 1) is not null
and ct.OID in (-1,1,2,104181,104182,104183)
order by truck_name
<snip>
This is just a theory but you may either be running out of memory -or-
you cannot allocate enough swap space -or- you don't have enough room
for temporary tables. This query, while legal, is not very practical.
It forces the query engine to loop through your database several times
*per row*
I suggest you break it into smaller queries and process the question in
stages.
stage 1 : figure out the list of most recent OIDs store those into a
temporary table (preferably in memory) limited to include only those
that match with your corresponding cust_trucks.OID list.
stage 2 : Using the table from stage 1 and some regular JOIN clauses you
can now derive the rest of the report
* date_time (adjusted by -360 minutes)
* location1distance
* location1
This way you won't need to do any dependent subqueries for your final
answer. Yes, they are a powerful tool but your original query is an
example of how that they can be used in ways that make them less than
effective.
Please let me know if you need additional guidance.
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
