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 st开发者_如何学Catement more faster?

---

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

---

Create an index on the CityCode column.

---

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...

---

• 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)

---

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.