MySQL: memory requirements for django app with 1 big table (460M rows) I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows).

CREATE TABLE `genescorrelation` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `correlation` double NOT NULL,
 `gene1_id` int(10) unsigned NOT NULL,
 `gene2_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `c_gene1_id_6b1d81605661118_fk_genes_gene_entrez`   (`gene1_id`),
KEY `gene2_gene1_idx1` (`gene2_id`,`gene1_id`)) 
ENGINE=InnoDB AUTO_INCREMENT=92345005 DEFAULT CHARSET=latin1


The query that user will run (up to 10 genes):

 SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation 
 WHERE gene2_id IN (176829, 176519, 176230) 
 GROUP BY gene1_id ORDER BY NULL 


Currently I have imported around 89M rows and the query is slow. It would take around 30-40 seconds for 3 or 4 genes.

I think my VM is too weak because it was a default set up (for smaller project/database). Currently it has just 2GB RAM and 36GB space.

I need to ask to set up prod machine.

What are the memory/space suggestion for this kind of set up?
Is there anything that could be done in MySQL config?
Is there anything else I can do to speed up a query? 

Any hints appreciated, thanks.

FYI, I asked my initial question on so: https://stackoverflow.com/questions/31969964/mysql-slow-avg-query-for-411m-rows. After that I was trying to add the suggested index (gene2, gene1) but didn't have enough space, so dropped the table and reimported 90M rows.
Looking at the query

SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation 
WHERE gene2_id IN (176829, 176519, 176230) 
GROUP BY gene1_id ORDER BY NULL 


I have four(4) suggestions

SUGGESTION #1

I see three(3) columns. I recommend making a covering index

ALTER TABLE genescorrelation
ADD INDEX gene2_gene1_correlation_idx1 (gene2_id,gene_id,correlation);


The StackOverflow accepted answer already mentioned making this index. That way, the aggregation never touches the table. All the needed data will come from the index.

Since you already made gene2_gene1_idx1 here is the fix

ALTER TABLE genescorrelation
DROP INDEX gene2_gene1_idx1,
ADD INDEX gene2_gene1_correlation_idx1 (gene2_id,gene_id,correlation);


SUGGESTION #2

You probably need more read threads

Add this to my.cnf

[mysqld]
innodb_read_io_threads = 8


Restart of mysql is required

SUGGESTION #3

If the server is just a DB Server, you should reserve up to 75% of RAM for InnoDB Buffer Pool or the sum total of all InnoDB Data and Indexes (SELECT SUM(data_length+index_length) InnoDBDataAndIndex FROM information_schema.tables WHERE engine='InnoDB';), whichever is smaller.

SUGGESTION #4 (OPTIONAL)

Change the Query to see break down by gene2_id

SELECT
    IFNULL(gene2_id,'All Gene2 IDs') gene2,
    IFNULL(gene1_id,'All Gene1 IDs') gene1,
    AVG(correlation) AS avg FROM genescorrelation 
WHERE gene2_id IN (176829, 176519, 176230) 
GROUP BY gene2_id,gene1_id WITH ROLLUP;