… round, since reportedly Dimitri was using 8 tables with 1M rows each (about 2.1GB) for his tests, while in our…: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0.5/lua: POINT_SELECT QPS test sysbench –num-threads=<1..1024…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…can easily fill TokuDB tables with 1bln of rows on this SSD, and projected InnoDB performance… is low selectivity is not good for fast inserts, but it is suitable for range selects by `…count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on –max-time=18000 –report-interval=10 –max-requests=0 –num…
Post: Handling big result sets
June 26, 2006 By 13 Comments
… KEY (`f1`) ); select count(*) from longf; +———-+ | count(*) | +———-+ | 5242880 | +———-+ And simple PHP script which retrieves all rows from the table: …result(); while ($stmt->fetch()) { } printf(“Number of rows: %d.\n”, $stmt->num_rows); /* free result */ $stmt->free_result(); /* …
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(*) FROM test_table; — 3 Tickets Used +———-+ | COUNT(*) | +———-+ | 3 | +———-+ 1 row …table-size=80000000 –oltp-read-only=off –init-rng=on –num-threads=16 –max-requests=0 –oltp-dist-type=uniform –…
Comment: ORDER BY ... LIMIT Performance Optimization
July 10, 2008 By 100 Comments
… PHP $sqls=”select id,state from states”; $rss=mysql_query($sqls) or die(mysql_error()); $num_rowss=mysql_num_rows($rss); $count=1; while($num_rowss && $datas=mysql_fetch_assoc($rss)) { $state_id=$datas['id']; $state=$datas['state']; $sqlc=”select * from…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
September 11, 2009 By 57 Comments
select count(*) from users group by city for this query if you use count this not will give you true result. in this case you have to use mysql_num_rows or SQL_CALC_FOUND_ROWS , mysql_num_rows runs very slow. we have only one option it is SQL_CALC_FOUND_ROWS. if…
Comment: Heikki Tuuri to answer your in depth Innodb questions
October 8, 2007 By 51 Comments
I know that SELECT COUNT(*) or COUNT(column) from InnoDB is not optimized because of how InnoDB does row versioning or something it does… with table_name/num_rows. However, couldn’t this easily be fixed in MySQL – if table handler is InnoDB, a select count() (with no WHERE clause, obviously) could read the row count from INFORMATION_SCHEMA or maybe just…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
…sysbench generates read/write activity: Screen0: root@localhost:~# sysbench –num-threads=16 –max-requests=0 –test=oltp –oltp-… all InnoDB tables are reachable: mysql> select count(*) from sbtest; +———-+ | count(*) | +———-+ | 1000000 | +———-+ 1 row in set (0.19 sec) Conclusions …
Comment: ORDER BY ... LIMIT Performance Optimization
August 2, 2007 By 100 Comments
… my logic to do random. $entries = array(); $count = 10; while ($count > 0) { $DB->query(“SELECT col1,col2 FROM entries AS e1 INNER JOIN…:{}’ ORDER BY e1.entryid ASC LIMIT 1″); if ($DB->get_num_rows() > 0) { $count–; $entries['entries'][] = $this->__processEntry($DB->fetch_array()); } } It works…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… as returned by MySQL 5.5 is as follows: SELECT o.*, count(*) as num_items FROM orders AS o INNER JOIN lineitem AS… BY num_items DESC LIMIT 10; +—-+————-+——-+——-+——————————————–+———+———+——————-+———+———-+———————————————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows… joining key from the table supplier would match approximately 600 rows from the table lineitem for each distinct key value, in…
