Databases
StackOverflow2013
Postsdbo
POoptimize mysql count query
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POoptimize mysql count query
    text
    copied!<p>Is there a way to optimize this further or should I just be satisfied that it takes 9 seconds to count 11M rows ?</p> <pre><code>devuser@xcmst &gt; mysql --user=user --password=pass -D marctoxctransformation -e "desc record_updates" +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | record_id | int(11) | YES | MUL | NULL | | | date_updated | datetime | YES | MUL | NULL | | +--------------+----------+------+-----+---------+-------+ devuser@xcmst &gt; date; mysql --user=user --password=pass -D marctoxctransformation -e "select count(*) from record_updates where date_updated &gt; '2009-10-11 15:33:22' "; date Thu Dec 9 11:13:17 EST 2010 +----------+ | count(*) | +----------+ | 11772117 | +----------+ Thu Dec 9 11:13:26 EST 2010 devuser@xcmst &gt; mysql --user=user --password=pass -D marctoxctransformation -e "explain select count(*) from record_updates where date_updated &gt; '2009-10-11 15:33:22' " +----+-------------+----------------+-------+--------------------------------------------------------+--------------------------------------------------------+---------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------+--------------------------------------------------------+--------------------------------------------------------+---------+------+----------+--------------------------+ | 1 | SIMPLE | record_updates | index | idx_marctoxctransformation_record_updates_date_updated | idx_marctoxctransformation_record_updates_date_updated | 9 | NULL | 11772117 | Using where; Using index | +----+-------------+----------------+-------+--------------------------------------------------------+--------------------------------------------------------+---------+------+----------+--------------------------+ devuser@xcmst &gt; mysql --user=user --password=pass -D marctoxctransformation -e "show keys from record_updates" +----------------+------------+--------------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------------+------------+--------------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+ | record_updates | 1 | idx_marctoxctransformation_record_updates_date_updated | 1 | date_updated | A | 2416 | NULL | NULL | YES | BTREE | | | record_updates | 1 | idx_marctoxctransformation_record_updates_record_id | 1 | record_id | A | 11772117 | NULL | NULL | YES | BTREE | | +----------------+------------+--------------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+ </code></pre> <p><strong>UPDATE</strong> - my solution is here: <a href="http://code.google.com/p/xcmetadataservicestoolkit/wiki/ResumptionToken">http://code.google.com/p/xcmetadataservicestoolkit/wiki/ResumptionToken</a></p>
 

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