SUMMARY:

Here are the issues I need resolved:

(a) how can I stop one query from putting a hold on all other simultaneous queries?
(b) Will switching from a MyISAM db to another storage engine improve general performance, given that I don't need locking?
(c) Can the code below be simplified?

MySQL version 4.1.14-nt

I set up this server myself, so I may have done something horribly wrong, but I haven't tried to tweak MySQL so I don't think I could have tweaked it in a non-productive way. It's on a Windows 2003 box with 1gb ram, running on an Athlon XP 3000+ processor.

I was running the query below on the table. The data was being fed, using XML, to a graphing program to graph the last 45 days worth of sales.

We had about 3,000 records. Then, I imported an additional 3,000 from the previous version of our shopping cart. I'm not sure whether that was the cause of the slow-down, but it seems like it was around the same time that there was a drastic slow-down in the graphing program. It always took 10 or so seconds to come up, but now it times out after 30 seconds.

I changed the query from 45 days to 7 days and now it only works sometimes in 30 seconds.

Also, when I run this query (and similar ones) our website, which is totally mysql -> xml -> xslt -> html powered, stops responding until it times out.

I have tried to define field lengths and field types to be as small as possible...

Some stats I found in MySQL CC:

Flush Tables: 1
Open Tables: 29
Opens: 153081
Queries Per Second Avg: 5.036
Questions: 30549484
Slow Queries: 512
Threads: 7
Uptime: 70 days 5 hours

Here's the query. The big problem with this query is that we need sums for every day for the last 45 days, even if that sum is zero. Of course, if it were zero (and we have domains that go a day without selling any... and some that do not) there would not be any rows in orders_summary for that day.

SELECT
concat(MonthName(order_date), ' ', dayofmonth(order_date)) as date,
year(order_date) as year, month(order_date) as month, dayofmonth(order_date) as day,
(
SELECT
ifnull(sum(total_charges), 0)
FROM
orders_summary inside
WHERE
domain_id = '" . $good_store_info['domain_id'] . "' AND
year(inside.order_date) = year(outside.order_date) AND
month(inside.order_date) = month(outside.order_date) AND
dayofmonth(inside.order_date) = dayofmonth(outside.order_date) AND
(
status = 'closed' OR
status = 'open' OR
status = 'new'
)
) as daily_sales
FROM
orders_summary outside
WHERE
order_date > DATE_SUB(NOW(),INTERVAL 15 DAY) AND
(
status = 'closed' OR
status = 'open' OR
status = 'new'
)
GROUP BY
year(order_date), month(order_date), dayofmonth(order_date)
ORDER BY
year, month, day

can you post your table structure, indices, and the results of an EXPLAIN on your query? (oh, and can you put the code in CODE tags ?)