Results 1 to 2 of 2

Thread: Query Speed

  1. #1
    Join Date
    Jul 2005

    Question Unanswered: Query Speed


    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.

    concat(MonthName(order_date), ' ', dayofmonth(order_date)) as date,
    year(order_date) as year, month(order_date) as month, dayofmonth(order_date) as day,
    ifnull(sum(total_charges), 0)
    orders_summary inside
    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
    orders_summary outside
    order_date > DATE_SUB(NOW(),INTERVAL 15 DAY) AND
    status = 'closed' OR
    status = 'open' OR
    status = 'new'
    year(order_date), month(order_date), dayofmonth(order_date)
    year, month, day

  2. #2
    Join Date
    Apr 2005
    Baltimore, MD
    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 ?)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts