…mysql> delete from dir_test_innodb LIMIT 200000; Query OK, 200000 rows affected (8.65 sec) mysql> optimize table dir_test_innodb…
Post: InnoDB Full-text Search in MySQL 5.6 (part 1)
February 26, 2013 By 1 Comment
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
… username, or even the rare count. But for the most …MySQL + Memcached, and then to other solutions like Tokyo Tyrant or Cassandra. My Application does the following: A.) read a row… loves memcached, it’s simple, fast, and just works. When …4GB. For these tests I left Innodb with a 256M buffer pool,…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… to prevent wide innodb primary keys on the MV. mysql> select mview$…COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql…Flexviews can help you ensure fast response times in your …
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 25 Comments
… size, and which is very fast for Innodb but by the fact MySQL by default looks to refresh Innodb statistics each time table is… count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count(*) | sum(data_length) | +———-+——————+ | 130 | 2856365892 | +———-+——————+ 1 row in set (1.08 sec) mysql> set global innodb_stats_on_metadata=0; Query OK, 0 rows affected (0.00 sec) mysql> select count(*),sum…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…MySQL version We used MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql…rows (more details with Jim Starkey’s comments in Part 2). There MyISAM shows bad scalability with increasing count…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
…as long as MySQL isn’t restarted it is possible to recover database fast. Let me…MySQL: root@localhost:/var/lib/mysql# /etc/init.d/mysql restart After the restart all InnoDB tables are reachable: mysql> select count(*) from sbtest; +———-+ | count(*) | +———-+ | 1000000 | +———-+ 1 row…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
…` (`c`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; select count(t1.pad),count(t2.pad) from t1,t1 t2 …Innodb primary key joins are very fast as data is clustered together with index and generally highly optimized Innodb…than one row which matches the index, so there is no need for MySQL to request next row …
Comment: COUNT(*) for Innodb Tables
July 19, 2012 By 32 Comments
… can get approximate number of rows as fast in Innodb as in myisam tables using “SHOW TABLE STATUS” command it …: Comment: customer table 1 row in set (0.00 sec) mysql> select count(*) from customers *************************** 1. row *************************** count(*): 11649 1 row in set (0.03 sec…
Comment: COUNT(*) for Innodb Tables
April 20, 2008 By 32 Comments
… of rows in a InnoDB table, this seems to work: explain select count(*) from Table; The result is fast and will give you a “rows” count that should be within 10% of the number of rows in the Table. So it seems the row count is being cached SOMEWHERE (I don’t know where). BTW, I am using MySQL…
Post: Percona Server 5.1.49-rel12.0
September 15, 2010 By 6 Comments
…Counts queries with very short execution times and groups them by time interval. (Oleg Tsarev) innodb…MySQL global system variable innodb_flush_log_at_trx_commit. (Yasufumi Kinoshita) innodb_fast_index_creation – Allows disabling of fast index creation. (Yasufumi Kinoshita) innodb…the rows to be …
