Avatar of mskumar_apk
mskumar_apk

asked on 

Mysql ultra fast aggregation

Dear Experts,

I have a mysql myisam table with the following columns

name (varchar), department (varchar), item (varchar), value (integer).

I will have 1 million rows in the above table for each and every ten minutes. From the above once in ten minutes I need to identify top 100 rows as below

select name,department,item,sum(value) as value from mytable group by name,department,item order by value desc limit 100;

Please let us know the ways through which I shall get lightning speed performance of the above query. I will be able to allocate as much hardware i can and interested in the following.

Table redesign
mysql tuning parameters
mysql in memory tables if have more RAM
Any other mysql specific functions

regards,
MSK
MySQL Server

Avatar of undefined
Last Comment
NovaDenizen
Avatar of Dejan Pažin
Dejan Pažin
Flag of Austria image


Put index on value column.
Avatar of mskumar_apk
mskumar_apk

ASKER

Hi,

putting index on value column does not going to help much as group by is based on name,department, item. It is not possible for me to put index on all the columns as it makes insert delayed.

MSK
Avatar of mattaob
mattaob
Flag of United Kingdom of Great Britain and Northern Ireland image

can you run

explain select name,department,item,sum(value) as value from mytable group by name,department,item order by value desc limit 100;

Insert will be delayed not by the indexes but by myisam table locking - if the table is innodb it will use row locking instead.
ASKER CERTIFIED SOLUTION
Avatar of NovaDenizen
NovaDenizen

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of NovaDenizen
NovaDenizen

Using ENGINE=MEMORY will make the table resident in RAM, and never get written to disk.  This means if mysqld shuts down then you will lose all the rows, but it sounds to me like you just throw them away and start over every 10 minutes, so it really wouldn't matter if you lost all of them.  

If you really need the entries durably stored, then I'd store them elsewhere, let this server just concentrate on the important aggregation query.
Avatar of virmaior
virmaior
Flag of United States of America image

replace your current design with two tables:

table_1:

ndi_id (int), name (varchar), department (varchar), item (varchar)

table_2:
ndiv_id (int), ndi_id (int),  value (int)


ndi_id is an auto-incrementing PK of table_1 and a FK for table_2.

now you just index ndi_id on table_2

wallah, massive speed up on the selects.



Avatar of mskumar_apk
mskumar_apk

ASKER

Hi All,

Thanks for your wonderful suggestions. I am using memory table at one level. But on the other levels I have to use myisam table and on explain it says  "using filesort using temporary".

Are we having any suggestions to improve the same? I am getting the same even I add index for all of the columns.

regards,
MSK
Avatar of virmaior
virmaior
Flag of United States of America image

msk -> it's going to be hard to avoid filesort and temporary on your current table design because you are asking for a SUM in your GROUP BY.  An index cannot store the SUM so it just helps in terms of organizing what it is going to have to read.

the filesort is made necessary by the ORDER BY.


one thing to consider is making your own TEMPORARY TABLE, indexing it, and then returning the TOP 100 entries rather than doing all of this in one query.
SOLUTION
Avatar of NovaDenizen
NovaDenizen

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
MySQL Server
MySQL Server

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo