Databases
StackOverflow2013
Postsdbo
POMysql count performance on very big tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql count performance on very big tables
    primarykey
    data
    text
    <p>I have a table with more than 100 millions rows in Innodb.</p> <p>I have to know if there is more than 5000 rows where the foreign key = 1. I don't need the exact number.</p> <p>I made some testing : </p> <p><i><code>SELECT COUNT(*) FROM table WHERE fk = 1</code></i> => 16 seconds<br/> <i><code>SELECT COUNT(*) FROM table WHERE fk = 1 LIMIT 5000</code></i> => 16 seconds<br/> <i><code>SELECT primary FROM table WHERE fk = 1</code></i> => 0.6 seconds<br/></p> <p>I will have a bigger network and treatment time but it can be an overload of 15.4 seconds !</p> <p>Do you have a better idea ?</p> <p>Thanks</p> <p><strong>Edit: [Added OP's relevant comments]</strong></p> <p>I tried SELECT SQL_NO_CACHE COUNT(fk) FROM table WHERE fk = 1 but it took 25 seconds</p> <p>Mysql was tuned for Innodb with Mysql Tuner.</p> <pre><code>CREATE TABLE table ( pk bigint(20) NOT NULL AUTO_INCREMENT, fk tinyint(3) unsigned DEFAULT '0', PRIMARY KEY (pk), KEY idx_fk (fk) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=100380914 DEFAULT CHARSET=latin1 </code></pre> <p>DB Stuff:</p> <pre><code>'have_innodb', 'YES' 'ignore_builtin_innodb', 'OFF' 'innodb_adaptive_hash_index', 'ON' 'innodb_additional_mem_pool_size', '20971520' 'innodb_autoextend_increment', '8' 'innodb_autoinc_lock_mode', '1' 'innodb_buffer_pool_size', '25769803776' 'innodb_checksums', 'ON' 'innodb_commit_concurrency', '0', 'innodb_concurrency_tickets', '500' 'innodb_data_file_path', 'ibdata1:10M:autoextend' 'innodb_data_home_dir', '', 'innodb_doublewrite', 'ON' 'innodb_fast_shutdown', '1' 'innodb_file_io_threads', '4' 'innodb_file_per_table', 'OFF', 'innodb_flush_log_at_trx_commit', '1' 'innodb_flush_method', '' 'innodb_force_recovery', '0' 'innodb_lock_wait_timeout', '50' 'innodb_locks_unsafe_for_binlog', 'OFF' 'innodb_log_buffer_size', '8388608' 'innodb_log_file_size', '26214400' 'innodb_log_files_in_group', '2' 'innodb_log_group_home_dir', './' 'innodb_max_dirty_pages_pct', '90' 'innodb_max_purge_lag', '0' 'innodb_mirrored_log_groups', '1' 'innodb_open_files', '300' 'innodb_rollback_on_timeout', 'OFF' 'innodb_stats_on_metadata', 'ON' 'innodb_support_xa', 'ON' 'innodb_sync_spin_loops', '20' 'innodb_table_locks', 'ON' 'innodb_thread_concurrency', '8' 'innodb_thread_sleep_delay', '10000' 'innodb_use_legacy_cardinality_algorithm', 'ON' </code></pre> <p><strong>Update '15:</strong> I used the same method up to now with 600 millions rows and 640 000 new rows per day. It's still working fine.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShotips
    1. UShotips
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMysql count performance on very big tables
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMysql count performance on very big tables
      1. USnischayn22
      1. VTFavorite
    3. VO
      singulars
      1. POMysql count performance on very big tables
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthe count would go faster if you chose a column in the `COUNT()`, as such: `SELECT COUNT(fk) FROM table WHERE fk = 1`
      singulars
      1. POMysql count performance on very big tables
      1. USClydeFrog
    2. COtake a look at [this website](http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/) for more information
      singulars
      1. POMysql count performance on very big tables
      1. USClydeFrog
    3. CO@ClydeFrog: Really? According to [the manual](http://dev.mysql.com/doc/en/group-by-functions.html#function_count), *`COUNT(*)` is optimized to return very quickly if the `SELECT` retrieves from one table, no other columns are retrieved, and there is no `WHERE` clause*. Indeed, the blog to which you linked suggests that `COUNT(*)` is faster than `COUNT(column)`.
      singulars
      1. POMysql count performance on very big tables
      1. USeggyal
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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