June 19, 2013

Post: Tools and Techniques for Index Design Webinar Questions Followup

October 4, 2012 By 5 Comments

…MySQL can use indexes, such as: Index merge improvements.  But I expect that defining the right compound index will still be superior. Index…a query, by counting how many lookups to index entries or table…select-list. The difference is that QUBE helps you to predict quantitatively how much a new index…

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

Post: Recovery deleted ibdata1

August 10, 2012 By 9 Comments

… INSERT BUFFER AND ADAPTIVE HASH INDEX ————————————- Ibuf: size 1, free …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

… 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… (0.00 sec) 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: , , , , ,

Comment: COUNT(*) vs COUNT(col)

April 25, 2007 By 45 Comments

… | wp_comments | index | NULL | rajidx | 9 | NULL | 424672 | Using index | +—-+————-+————-+——-+—————+——–+———+——+——–+————-+ 1 row in set (0.00 sec) mysql> select count(*) from wp_comments; +———-+ | count(*) | +———-+ | 230120…, so its not actually counting the values but the pointers from this index: mysql> select count( distinct (comment_approved) ) from wp_comments; +————————————–+ | count( distinct (comment_approved…

Filed Under: Insight for Developers Tagged With: ,

Post: Indexes in MySQL

June 2, 2006 By 29 Comments

…) d1, (SELECT count(*) cnt2 FROM t2 WHERE ID1=1) d2; 0.9492 = 94.92% Explain still claims MySQL will use index. Execution time: SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1 – 1200 ms SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX (ID1) WHERE…

Filed Under: Insight for DBAs Tagged With: , ,

Post: When EXPLAIN estimates can go wrong!

October 13, 2011 By 7 Comments

…: Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where type=6 \G *************************** 1. row *************************** id: 1 select_type… rows: 339184 Extra: Using where; Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where type between 3…

Filed Under: MySQL

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

February 26, 2013 By 1 Comment

…indexes, it’s entirely possible to DROP multiple FT indexes…mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select… | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize_…

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

Post: Performance Schema tables stats

May 2, 2011 By 2 Comments

…mysql> select OBJECT_NAME,COUNT_STAR from table_io_waits_summary_by_table order by COUNT_STAR DESC LIMIT 5; +————-+————+ | OBJECT_NAME | COUNT…. There is table with statistic per index: mysql> select * from table_io_waits_summary_by_index_usage where OBJECT_NAME=’sbtest55′G …

Filed Under: MySQL

Post: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

…mysql> select count(distinct c) from antest; +——————-+ | count(distinct c) | +——————-+ | 101 | +——————-+ 1 row in set (0.36 sec) mysql> select count(distinct i) from antest; +——————-+ | count…

Filed Under: Insight for DBAs Tagged With: ,

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

… referring to the amazing MySQL manual, especially the option and variable reference table….privblogpercona.commanual Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select_scanblogpercona….

Filed Under: Insight for DBAs, MySQL
« Older Posts
Newer Posts »