MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Fredrik Högberg Date:March 13 2004 2:09pm
Subject:MySQL takes 8Sek, MySQLcc takes 0.1Sek to run the same query. WHY???
View as plain text  
Hi!
I'm trying to run the following query.

"(SELECT 
Priority, OnDateExt, OnTime, OnMilli, OffDateExt, OffTime, Name, Native_SumDesc, Comment 
FROM history.alarm_summary 
WHERE OnDateExt > DATE_SUB(CURDATE(), INTERVAL 14 DAY) )
UNION 
(SELECT 
Priority, OnDateExt, OnTime, OnMilli, OffDateExt, OffTime, Name, Native_SumDesc, Comment 
FROM history.event_summary 
WHERE OnDateExt > DATE_SUB(CURDATE(), INTERVAL 14 DAY) )

ORDER BY 2 DESC, 3 DESC, 4 DESC 
LIMIT 0,38 "


The alarm_summary and event_summary contains about 150k records and OnDateExt, OnTime,
OnMilli is indexed.

The fun part is that this this query takes about 8-10 sek to complete with excel(import
external data), the mysql client (command prompt) and a using odbc mfc classes but with
MySQLcc it takes only 0.1sek to run???
I've tried to change the query so there's no possibility that MySQLcc has cached something
but cannot make mysqlcc run the query under 0.2 sek.

Can anybody have any clue on why this is happening.

This is a quite serious issue because if the query takes 8sek i have to seriously rethink
things to make it run faster because the mysql server is running at high cpu usage during
the query and i have other tasks at that computer that also needs a little cpu time.
Perhaps move the last 14 days of data into a separate table instead of running the query
in the large table but how can the mysqlcc be that fast???, it has to be an illusion but i
cant figure out why i'm seeing it.

Any thoughts

Cheers 
Fredrik Högberg
Thread
MySQL takes 8Sek, MySQLcc takes 0.1Sek to run the same query. WHY???Fredrik Högberg13 Mar
  • Re: MySQL takes 8Sek, MySQLcc takes 0.1Sek to run the same query.WHY???T Cunningham15 Mar