running same query on console taking time twice?

Question

I am running a query -

SELECT COUNT(DISTINCT c.msisdn) 
FROM cdr c 
WHERE c.evedate>='2013-10-01';

On executing first time, taking 1 minute. But executing the same query within second taking 3 minutes.

Why is it happening?

I have found the same behavior with other queries too.

Details if required:
My machine have 4 GB RAM
Mysql 5.5 version.
key_buffer_size = 350M.

This table have 140 million records with 10 partitions.

Thank you.

so the first time it takes 1' and the second time it takes 3', or vice versa? — Zsolt Szilagyi, Dec 09 '13 at 09:04
Please use EXPLAIN on our query, then execute it, then use EXPLAIN again. I assume that the optimiser goes havoc. ANALYSE could be a solution. — Zsolt Szilagyi, Dec 09 '13 at 09:34

DRapp · Accepted Answer · 2013-12-09T12:33:52.680

1

Do you have an index on

 ( eveDate, msisdn )

This would make it a covering index and not have to go to the actual data pages for the ID, yet be query optimized by the "eveDate" portion of the where clause.

Suggestion per feedback.

If the data is date-based, I would suggest having a separate table that had nothing but the date in question (or hourly if that might be better for your work as you stated you had an index by the hour) and a count of IDs. If the day's activity is not going to change (or rarely), this table just sits there with a count. Even if you ran it nightly to update counts on a per day/hour basis, such as for the last 10 days, your subsequent queries would be almost instantaneous.

Additionally, you might even just create a trigger on the table in question with all these records. As a new record is added, it just does an insert/update to the counters table something like...

update CountersSummary
   set TotalCount = TotalCount +1
   where eveDate = just date portion of DateTimeValueOfNewRecord
      and eveHour = hour of DateTimeValueOfNewRecord

if the record count updated = 0
   insert into CountersSummary ( eveDate, eveHour, TotalCount )
      values ( just date portion of DateTimeValueOfNewRecord,
               hour of DateTimeValueOfNewRecord,
               1 )
end if

edited Dec 09 '13 at 12:33

answered Dec 09 '13 at 11:10

DRapp

47,638
12
72
142

yes i have same index,and also have - (evedate_hour) and in explain plan its using the same index (evedate,msisdn) but still taking 3 minutes to execute. – Aamir Dec 09 '13 at 11:17
How is the partitioning setup, and how many records as of 2013-10-01 per sample query... roughly – DRapp Dec 09 '13 at 11:18
partitions are based on weekly basis, i have 2,400,000 records or each date(2013-10-01), i have total 2 month records, and partition is based on evedate. – Aamir Dec 09 '13 at 11:49
@Aamir0731, see revision option based on your volume of data – DRapp Dec 09 '13 at 12:34
In this table we are inserting(24x7) 30000 records in every 15 minutes, so i think trigger or update is not gonna help me. – Aamir Dec 09 '13 at 13:20
1

Then maybe some timer process that does a select SUM() based on each prior hour? Almost anything would be better than requerying 2.4mil records every inquiry. – DRapp Dec 09 '13 at 13:22
well I am going to test this all three things you told me. and what about the key buffer size and other parameters? how I should set them to get maximum use? This table is 16GB big and i have only 4 gb ram – Aamir Dec 09 '13 at 14:01
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/42790/discussion-between-drapp-and-aamir0731) – DRapp Dec 09 '13 at 14:05

running same query on console taking time twice?

1 Answers1