June 18, 2013

Post: More on MySQL transaction descriptors optimization

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

Post: Why MySQL could be slow with large tables ?

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)…

Post: UDF -vs- MySQL Stored Function

… 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(*) | +———-+ | …

Post: How fast can MySQL Process Data

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_…

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

…`) ) 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…

Post: Recovery deleted ibdata1

speed up flushing we can set dirty pages percentage to zero: mysqlINDEX ————————————-…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

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

… 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…

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

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…

Post: MySQL Indexing Best Practices: Webinar Questions Followup

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

Post: How much memory Innodb locks really take ?

…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 …