Is there a better index to speed up this query?

Question

The following query uses temporary and filesort. I would like to avoid this if possible.

SELECT lib_name, description, count(seq_id), floor(avg(size)) 
FROM libraries l JOIN sequence s ON (l.lib_id=s.lib_id)
WHERE s.is_contig=0 and foreign_seqs=0 GROUP BY lib_name;

EXPLAIN He speaks:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,s,ref,libseq,contigs,contigs,4,const,28447,Using temporary; Using filesort
1,SIMPLE,l,eq_ref,PRIMARY,PRIMARY,4,s.lib_id,1,Using where

The tables look like this:

libraries

CREATE TABLE  `libraries` (
  `lib_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lib_name` varchar(30) NOT NULL,
  `method_id` int(10) unsigned DEFAULT NULL,
  `lib_efficiency` decimal(4,2) unsigned DEFAULT NULL,
  `insert_avg` decimal(5,2) DEFAULT NULL,
  `insert_high` decimal(5,2) DEFAULT NULL,
  `insert_low` decimal(5,2) DEFAULT NULL,
  `amtvector` decimal(4,2) unsigned DEFAULT NULL,
  `description` text,
  `foreign_seqs` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 means the sequences in this library are not ours',
  PRIMARY KEY (`lib_id`),
  UNIQUE KEY `lib_name` (`lib_name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

sequences

CREATE TABLE  `sequence` (
  `seq_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_name` varchar(40) NOT NULL DEFAULT '',
  `lib_id` int(10) unsigned DEFAULT NULL,
  `size` int(10) unsigned DEFAULT NULL,
  `add_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sequencing_date` date DEFAULT '0000-00-00',
  `comment` text DEFAULT NULL,
  `is_contig` int(10) unsigned NOT NULL DEFAULT '0',
  `fasta_seq` longtext,
  `primer` varchar(15) DEFAULT NULL,
  `gc_count` int(10) DEFAULT NULL,
  PRIMARY KEY (`seq_id`),
  UNIQUE KEY `seq_name` (`seq_name`),
  UNIQUE KEY `libseq` (`lib_id`,`seq_id`),
  KEY `primer` (`primer`),
  KEY `sgitnoc` (`seq_name`,`is_contig`),
  KEY `contigs` (`is_contig`,`seq_name`) USING BTREE,
  CONSTRAINT `FK_sequence_1` FOREIGN KEY (`lib_id`) REFERENCES `libraries` (`lib_id`)
) ENGINE=InnoDB AUTO_INCREMENT=61508 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

Are there any changes I can make to make the request faster? If not, then when (for a web application) is it worth putting the results of such a query, as indicated above, in the MEMORY table?

Answer 1

First strategy: speed up for mySQL searching for the records you want to generalize.

You already have a pointer to sequence.is_contig. You can try indexing libraries.foreign_seqs libraries. I don't know if this will help, but it's worth a try.

: , , . sort_buffer_size. , .

: , , . , , . . , , . , , . , , .

: , myISAM. .