…MySQL master database and populate it with some data. CREATE TABLE `t1` ( `c1` int(11) NOT NULL…select NULL from t1; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 … insert into repl.t1 select NULL… fields omitted): SHOW SLAVE STATUS\G Relay_Log_File: mysql_sandbox25162…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
…see main (fact) table which contains search fields and dimension tables which contain more …NOT NULL auto_increment, `pad` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) mysql> select count(*) from dim; +———-+ | count(*) | +———-+ | 30720 | +———-+ 1 row in set (0.00 sec) mysql> select…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… can be filtered on, and there are also bunch of fields which sorting can be performed on such as seller, price…` ( `cat_id` int(10) unsigned NOT NULL, `seller_id` int(10) unsigned NOT NULL, `price` decimal(10,2) NOT NULL, KEY `cat_id` (`cat_id`,`price`), KEY `cat_id_2` (`cat_id`,`seller_id` ) mysql> explain select…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…field from the table which is not…MySQL still will not be able to use it as Optimizer is not smart enough yet to detect such cases: mysql> explain select…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…SELECT with appropriate where clause. This however would not tell you full story, especially now as MySQL…not available even if it is well known – for example BLOB fields…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 29 Comments
… SELECT queries might have to scan through lots of obsolete, but not-yet…test ( col1 bigint(20) NOT NULL, col2 bigint(20) default NULL, key(col1), key(col2)…infile.txt’ into table load_test fields terminated by ‘\t’ lines terminated by ‘\n… -e /tmp/my-fifo ]; do time mysql -e “set foreign_key_checks=…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
… query Q0 is: select avg(c1) from (select year,month,count(*) …NULL, `Div5WheelsOff` varchar(10) DEFAULT NULL, `Div5TailNum` varchar(10) DEFAULT NULL ) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1; Last fields starting with “Div*” are not… each field. After that load statement is: mysql -S /tmp/mysql-ib….
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
…NULL | NULL | NULL | NULL | 1 | | | 1 | SIMPLE | j2 | system | NULL | NULL | NULL | NULL | 1 | | | 1 | SIMPLE | j3 | system | NULL | NULL | NULL | NULL | 1 | | +—-+————-+——-+——–+—————+——+———+——+——+——-+ 3 rows in set, 1 warning (0.00 sec) mysql…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…: NULL key: NULL key_len: NULL ref: NULL rows: 300363 Extra: Using filesort Surprising? The optimizer preferred a full table scan, and it did not… let’s modify the query by selecting only the first_name field instead of selecting all the fields: mysql> explain SELECT first_name FROM employees ORDER…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…not_cached’, ’294363493′ ‘Qcache_queries_in_cache’, ’177′ ‘Qcache_total_blocks’, ’498′ ‘Questions’, ’1462755007′ ‘Rpl_status’, ‘NULL‘ ‘Select_full_join’, ’0′ ‘Select…mysql.sock pid-file=/var/run/mysql/mysql.pid datadir=/db/disk2/mysql/data log-bin=/var/log/mysql/log log-slow-queries=/var/log/mysql…
