CODEWITHSUNDEEP
mysqlsql
emma
emma

Reputation: 51

How to make my MySQL SUM() query more faster

I have about 1 million rows on users table and have columns A AA B BB C CC D DD E EE F FF by example to count int values 0 & 1

SELECT 
CityCode,SUM(A),SUM(B),SUM(C),SUM(D),SUM(E),SUM(F),SUM(AA),SUM(BB),SUM(CC),SUM(DD),SUM(EE),SUM(FF)
FROM users 
GROUP BY CityCode

Result 8 rows in set (24.49 sec).

How to make my statement more faster?

Upvotes: 2

Views: 8343

Answers (5)

MarkR
MarkR

Reputation: 63538

You have no WHERE clause, which means the query has to scan the whole table. This will make it slow on a large table.

You should consider how often you need to do this and what the impact of it being slow is. Some suggestions are:

  • Don't change anything - if it doesn't really matter
  • Have a table which contains the same data as "users", but without any other columns that you aren't interested in querying. It will still be slow, but not as slow, especially if there are bigger ones
  • (InnoDB) use CityCode as the first part of the primary key for table "users", that way it can do a PK scan and avoid any sorting (may still be too slow)
  • Create and maintain some kind of summary table, but you'll need to update it each time a user changes (or tolerate stale data)

But be sure that this optimisation is absolutely necessary.

Upvotes: 0

Sharique
Sharique

Reputation: 4219

Use explain to to know the excution plan of your query.

Create atleast one or more Index. If possible make CityCode primary key.

Try this one

SELECT CityCode,SUM(A),SUM(B),SUM(C),SUM(D), SUM(E),SUM(F),SUM(AA),SUM(BB),SUM(CC),SUM(DD),SUM(EE),SUM(FF)
FROM users 
GROUP BY CityCode,A,B,C,D,E,F,AA,BB,CC,DD,EE,FF

Upvotes: 2

Tudor Constantin
Tudor Constantin

Reputation: 26861

  • Use better hardware
  • increase caching size - if you use InnoDB engine, then increase the innodb_buffer_pool_size value
  • refactor your query to limit the number of users (if business logic permits that, of course)

Upvotes: 1

Dmitri Gudkov
Dmitri Gudkov

Reputation: 2123

I believe it is not because of SUM(), try to say select CityCode from users group by CityCode; it should take neary the same time...

Upvotes: 2

Xint0
Xint0

Reputation: 5389

Create an index on the CityCode column.

Upvotes: 2

Related Questions