… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
… DEFAULT CHARSET=latin1 mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(val) from fact; +————+ | count(val) | +————+ | 7216582… COUNT(val). Why ? Because val column is not defined as NOT NULL there can be some NULL values in it and so MySQL… needed because column can be NULL. Now lets try few more queries: mysql> select count(*) from fact where i explain select count(*) from fact…
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…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise… the number of tmp tables created in memory vs on-disk, percentage of queries that needed full…Count of number of times this query was executed. Now let’s take a look at the values in the 95% column…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 6 Comments
…It was couple very simple things – (1) two varchar columns needed leading nonalpha characters trimmed, so “123 ^&* and …: mysql> select count(*) from paintings where title != ltrim_junk(title); +———-+ | count(*) | +———-+ | 101533 | +———-+ 1 row in set (6.82 sec) mysql> select count…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… used MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql-5… in Part 2). There MyISAM shows bad scalability with increasing count of thread. I think the reason is pread system call… count(id) FROM $tableName WHERE id between %d and %d MyISAM scales good here, because of access only to key column and…
Comment: COUNT(*) for Innodb Tables
December 20, 2011 By 29 Comments
… system so the count will increase). Then I counted a column that is a key from another table, not null : mysql> desc messages; +—————+————–+——+—–+———+—————-+ | Field… it’s due to the difference in field type (bigint vs int).
Comment: Air traffic queries in InfiniDB: early alpha
November 3, 2009 By 19 Comments
…first pass at your observation: LOAD DATA INFILE vs. InfiniDB bulk load (cpimport): We provided support …support the maximum amount of syntax for existing MySQL installations. We have observed that it performs …columns are NULL in the input datasets. They use 0 bytes in the input file (1 if you count…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… created the table with over 30 millions of rows. “val” column in this table has 10000 distinct value, so range 1… vs range scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… be fully indexed. An extra problem comes from the fact MySQL prefers when it is possible to use index for further… be quite serious. To fix this issue MySQL would need to better take into account column selectivity together with LIMIT range. If… hint. The other problem you may have however is calculating count of matching rows which may be even trickier to slow…
