StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimal MySQL settings for queries that deliver large amounts of data?
text
Body
copied!<p>I work as a scientist and I have used MySQL as a storage for the results of my numerical simulations. Typically I have a set of data obtained by my experiment and a control set. These two data sets are stored in one table. One indicator field tells me if a record comes from experiment or from a control set. This table usually has ~ 100 million records. 50million experiments and 50 million controls. </p> <p>When I do the post processing of my data my typical task consists of first issuing the following two queries:</p> <pre><code>select b0,t0 from results_1mregr_c_ew_f where RC='E' and df>60 /// getting experiments data </code></pre> <p>and</p> <pre><code>select b0,t0 from results_1mregr_c_ew_f where RC='C' and df>60 /// getting controls data </code></pre> <p>I have a multi column index on RC,df. These queries take lots of time and the queries spend most of the time "Sending data"</p> <p>I'm running this on 8core MacPro with 12GB of RAM. I'm a single user of this machine and this task is the primary task therefore I can dedicate all the RAM to MySQL. All tables are MyISAM (I can convert them if that would increase teh speed of my queries).</p> <p>I would appreciate any recommendations on how to speed up these queries. Should I change some settings, indices, queries....</p> <p><strong>In each of these queries I expect to get back ~ 50 million records.</strong> Note that splitting the table into two tables one containing experimental and one containing control observation is not an option due to administrative reasons. </p> <p>Here is the output of:</p> <pre><code>explain select b0, t0 from results_1mregr_c_ew_f where RC="C" and df>60 +----+-----------+---------------------+-----+-------------+---+-------+----+-------+-----------+ | id |select_type|table |type |possible_keys|key|key_len|ref |rows |Extra | +----+-----------+---------------------+-----+-------------+---+-------+----+-------+-----------+ | 1 |SIMPLE |results_1mregr_c_ew_f|range|ff |ff |11 |NULL|6251121|Using where| +----+-----------+---------------------+-----+-------------+---+-------+----+-------+-----------+ </code></pre> <p>Here is the output from:</p> <pre><code>show indexes from results_1mregr_c_ew_f; +-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | results_1mregr_c_ew_f | 0 | PRIMARY | 1 | id | A | 50793996 | NULL | NULL | | BTREE | | | results_1mregr_c_ew_f | 1 | ff | 1 | RC | A | 3 | NULL | NULL | | BTREE | | | results_1mregr_c_ew_f | 1 | ff | 2 | df | A | 120 | NULL | NULL | | BTREE | | +-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ </code></pre> <p>Here is the output from:</p> <pre><code>CREATE TABLE `results_1mregr_c_ew_f` ( `b0` double NOT NULL COMMENT ' ', `s0` double NOT NULL, `t0` double NOT NULL, `b1` double NOT NULL, `s1` double NOT NULL, `t1` double NOT NULL, `b2` double NOT NULL, `s2` double NOT NULL, `t2` double NOT NULL, `b3` double NOT NULL, `s3` double NOT NULL, `t3` double NOT NULL, `b4` double NOT NULL, `s4` double NOT NULL, `t4` double NOT NULL, `AD` char(4) NOT NULL, `chisq` double NOT NULL, `RC` char(7) NOT NULL, `colq` varchar(255) NOT NULL, `df` int(11) NOT NULL, `ncol` int(11) NOT NULL, `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `p1` float NOT NULL, `p2` float NOT NULL, `p3` float NOT NULL, `p4` float NOT NULL, PRIMARY KEY (`id`), KEY `ff` (`RC`,`df`) ) ENGINE=MyISAM AUTO_INCREMENT=50793997 DEFAULT CHARSET=ascii | </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload