June 18, 2013

Post: More on MySQL transaction descriptors optimization

April 26, 2013 By Leave a Comment

…select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0.5/lua: POINT_SELECT…mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects…

Filed Under: Benchmarks, MySQL Tagged With: ,

Post: Why MySQL could be slow with large tables ?

June 9, 2006 By 141 Comments

… speed up accesses to database. Some people would also remember if indexes are helpful or not depends on index selectivity -… by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count(pad)…

Filed Under: Insight for DBAs Tagged With: ,

Post: UDF -vs- MySQL Stored Function

September 6, 2010 By 8 Comments

… minutes. Then we added another MySQL function for different set of attributes and indexing speed went from 16 minutes to 26… speed I could even scan the whole table of 7 million records: mysql> select count(*) from paintings where title != ltrim_junk(title); +———-+ | count(*) | +———-+ | …

Filed Under: MySQL Tagged With: , ,

Post: How fast can MySQL Process Data

April 9, 2008 By 3 Comments

…speed stays about the same: mysql> select count(*) from m3; +———-+ | count(*) | +———-+ | 10476840 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from m3 where i>0; +———-+ | count…Data_length: 685609952 Max_data_length: 948528873 Index_length: 0 Data_free: 0 Auto_…

Filed Under: Benchmarks Tagged With:

Post: Using delayed JOIN to optimize count(*) and LIMIT queries

April 6, 2007 By 22 Comments

…`) ) mysql> select count(*) from dim; +———-+ | count(*) | +———-+ | 30720 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact where i select count(*) from fact left…

Filed Under: Insight for Developers Tagged With: ,

Post: Recovery deleted ibdata1

August 10, 2012 By 9 Comments

…speed up flushing we can set dirty pages percentage to zero: mysql… INDEX ————————————-…mysql# And restart 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…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

… disk problem, I pointed the MySQL 5.6.10 at the MySQL 5.5.30 data directory.  Tthe speed was essentially the same… in pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count(*) pages, sum…

Filed Under: MySQL Tagged With: , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… speed up bulk loading into an InnoDB FT index by…mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select…1028261 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize_end…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

…SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition_id; be slower without an index…mysql use index for group by? A: If you have Index on the column MySQL…

Filed Under: Insight for DBAs, MySQL

Post: How much memory Innodb locks really take ?

July 13, 2006 By 11 Comments

…Data_length: 100253696 Max_data_length: 0 Index_length: 128974848 Data_free: 0 Auto_…mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count(i) from sample for update; +———-+ | count(i) | +———-+ | … pretty old box, which means locking speed will be few millions per second …

Filed Under: Insight for DBAs Tagged With:
« Older Posts