CODEWITHSUNDEEP
mysql
luqita
luqita

Reputation: 4077

Optimizing a select from a large table

I have a table with around 40 million rows, and I want to run something like this:

SELECT country, count(*) FROM `signups`
where `signed_up` > '2012-03-20 00:00:00'
group by country

Basically to get how many sign ups per country after certain dates (usually for the last week). There are around 400K sign-ups per day, and 40 million in total or so.

The query just doesn't run for the last week, I get a 'MySQL server has gone away'... Any ways of optimizing this?

Upvotes: 0

Views: 109

Answers (2)

Kris.Mitchell
Kris.Mitchell

Reputation: 998

Counting * is a hit to the perfomance. It's always better, if possible, to not do * anything. It's just caused the DB engine to do more work, thus slowing the query down.

Alok is right, add an index to the columns you generally search on and the hash table will already be created when the query runs. Otherwise the engine will need to create the hash table of the results before it can actually get the results. The index does this before hand.

Upvotes: 1

Alok
Alok

Reputation: 36

Please use below query and add a index on signed_up column. You will surely get performance gain.

SELECT country, count(signup_id) FROM signups where signed_up > '2012-03-20 00:00:00' group by country

Regards, Alok

Upvotes: 2

Related Questions