June 20, 2013

Post: Why MySQL could be slow with large tables ?

June 9, 2006 By 141 Comments

…rows. “val” column in this table has 10000 distinct value, so range 1..100 selects…mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select…

Filed Under: Insight for DBAs Tagged With: ,

Post: Missing Data - rows used to generate result set

July 20, 2008 By 15 Comments

… so the number of rows accessed is same as number of rows used to generate result …MYSQL%” you may instead use Full Text Search indexes. If you have WHERE ID%100=0 you can have extra column…SELECT * FROM (SELECT COUNTRY,COUNT(*) FROM PEOPLE GROUP BY COUNTRY) C WHERE COUNTRY=’USA’ As of MySQL 5.0 MySQL…

Filed Under: Insight for Developers Tagged With:

Post: Troubleshooting MySQL Memory Usage

March 21, 2012 By 5 Comments

… to see MySQL memory consumption plotted. I use “VSZ” columns from “ps” …can do better as you can query temporary tables too: mysql> select sum(data_…and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID:…

Filed Under: Insight for DBAs, MySQL

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…MySQL manual, especially the option and variable reference table. But just as…column…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…

Filed Under: Insight for DBAs, MySQL

Post: pt-online-schema-change and default values

June 21, 2012 By 1 Comment

… default value. MySQL will assign empty string as default default value for varchar column. This …) Error copying rows from `sbtest`.`sbtest` to `sbtest`.`_sbtest_new`: Copying rows caused a MySQL error 1364…sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`…

Filed Under: Insight for DBAs, MySQL

Post: Be productive with the MySQL command line

December 21, 2012 By 14 Comments

…column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select count(*) from sakila; ERROR 1146 (42S02): Table ‘sakila.sakila’ doesn’t exist mysql> select count(*) from film; +———-+ | count(*) | +———-+ | 1000 | +———-+ 1 row…

Filed Under: Insight for DBAs, MySQL

Post: Percona XtraDB Cluster reference architecture with HaProxy

June 20, 2012 By 63 Comments

… transfer from ‘*any*’. Selected 1 (ip-10-112-39-98)(SYNCED) as donor. 120619 13:20:…rows affected (0.00 sec) Populate the table with data for the benchmark. # sysbench –test=oltp –db-driver=mysql –mysql-engine-trx=yes –mysql…is running. If you look at Cur column under Session, you can see, that …

Filed Under: MySQL, Percona Software, XtraDB Cluster Tagged With:

Post: Multi Column indexes vs Index Merge

September 19, 2009 By 31 Comments

…columns i1 and i2 independent in this case each selecting about 1% rows from this table which contains about 10M rows. mysql…const,const | 665 | | +—-+————-+———+——+—————-+———-+———+————-+——+——-+ 1 row in set (0.00 sec) As you can see MySQL …

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

… an indexed column The SQL used for this test together with its EXPLAIN output as returned by MySQL 5.5 is as follows: SELECT s… non-indexed column The SQL used for this test together with its EXPLAIN output as returned by MySQL 5.5 is as follows: SELECT s… BKA when you are joining a really small subset of rows, as then scanning the right-side table becomes costly in comparison…

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

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…somehow be “better” (as much as it’s actually possible…column named “value”. OK, sounds easy enough: mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row…rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb_myisam_stopword; Query OK, 543 rows…

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