Novini.net: MySQL storage engines comparison, include TokuDB and PBXT [Non finance article]

Novini.net: MySQL storage engines comparison, include TokuDB and PBXT [Non finance article]

Merry XMas!

Merry Christmas and Happy New 2011 Year!

Весела коледа и Честита Нова 2011 Година!

Bug in TokuDB

I just found a bug on MariaDB version. I was able to replicate it 4-5 times.

MariaDB [test]> show create table video;

CREATE TABLE `video` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `artist` varchar(100) NOT NULL,
  `title` varchar(100) NOT NULL,
  `video` varchar(20) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `video_artist_ix` (`artist`),
  KEY `video` (`video`),
  KEY `date_id` (`date`,`id`)
);



MariaDB [test]> create table x_toku(
 `id` int(4) NOT NULL AUTO_INCREMENT,
 `artist` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `video_artist_ix` (`artist`) 
) ENGINE=TOKUDB ;
Query OK, 0 rows affected (18.51 sec)



MariaDB [test]> insert into x_toku select id, artist from video;
Query OK, 10875440 rows affected (54.39 sec)
Records: 10875440  Duplicates: 0  Warnings: 0



MariaDB [test]> delete from x_toku where artist like 'ma%';
Query OK, 274705 rows affected (22.49 sec)



MariaDB [test]> select count(*) from x_toku where artist like 'ma%';
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
*** THIS MUST BE WRONG, DIDN'T WE DELETED EVERYTHING ***



*** THE DATA PROBABLY COMES FROM THE INDEX ***
MariaDB [test]> explain select count(*) from x_toku where artist like 'ma%';
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | x_toku | range | video_artist_ix | video_artist_ix | 102     | NULL |    2 | Using where; Using index |
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
*** YEP, USING INDEX ***



*** LETS TRY WITHOUT INDEX SCAN ***
MariaDB [test]> select count(*) from x_toku ignore index(video_artist_ix) where artist like 'ma%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (5.51 sec)
*** THIS STILL MUST BE WRONG, DIDN'T WE DELETED EVERYTHING ***



*** LETS SEE THE DATA ***
MariaDB [test]> select * from x_toku where artist like 'ma%';
+---------+------------------+
| id      | artist           |
+---------+------------------+
| 7953132 | mallu+adult2     |
| 6640134 | Martin+Nieverra1 |
+---------+------------------+
2 rows in set (0.00 sec)
*** HMMM ***



*** MAY BE IT GETS THE DATA IS MATERIALIZED FROM THE INDEX ***
MariaDB [test]> explain select * from x_toku where artist like 'ma%';
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | x_toku | range | video_artist_ix | video_artist_ix | 102     | NULL |    2 | Using where; Using index |
+----+-------------+--------+-------+-----------------+-----------------+---------+------+------+--------------------------+
1 row in set (0.03 sec)
*** YEP, USING INDEX ***



*** LETS SEE THE DATA WITHOUT INDEX SCAN ***
MariaDB [test]> select * from x_toku ignore index(video_artist_ix) where artist like 'ma%';
+----------+----------------------------------+
| id       | artist                           |
+----------+----------------------------------+
| 15292754 | Matt+Pokora+Catch+Me+If+You+Can1 |
+----------+----------------------------------+
1 row in set (5.24 sec)
*** DOUBLE HMMM ***


*** LETS SEE WHAT RECORD IS ACTUALLY REAL ***
MariaDB [test]> select * from x_toku where id in (7953132,6640134,15292754);
+----------+----------------------------------+
| id       | artist                           |
+----------+----------------------------------+
| 15292754 | Matt+Pokora+Catch+Me+If+You+Can1 |
+----------+----------------------------------+
1 row in set (0.00 sec)

Monty says: A quick look at MySQL 5.5 GA

MySQL 5.5 is GA now.
I will soon check it more carefully, but here is what Monty says :)
Monty says: A quick look at MySQL 5.5 GA

MySQL bug and Optimization of nested query

There is strange bug in MySQL, it does not use primary key, if the value is NULL. Instead it do full table scan. Here is an example.
Copy / Paste this in text editor to see the correct post.

---

MariaDB [gateway]> explain select * from crontab where id = (select crontab from items where id = 19465650);
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | crontab | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
|  2 | SUBQUERY    | items   | const | PRIMARY       | PRIMARY | 4       |       |    1 |       |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)


MariaDB [gateway]> explain select * from crontab where id = (select crontab from items where id = @id);
+----+----------------------+---------+------+---------------+------+---------+------+---------+-----------------------------------------------------+
| id | select_type          | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra                                               |
+----+----------------------+---------+------+---------------+------+---------+------+---------+-----------------------------------------------------+
|  1 | PRIMARY              | crontab | ALL  | NULL          | NULL | NULL    | NULL | 3854804 | Using where                                         |
|  2 | UNCACHEABLE SUBQUERY | NULL    | NULL | NULL          | NULL | NULL    | NULL |    NULL | Impossible WHERE noticed after reading const tables |
+----+----------------------+---------+------+---------------+------+---------+------+---------+-----------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [gateway]> explain
select
   *
from
   crontab,
   items
where
   items.crontab = crontab.id and
   items.id      = @id;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Indexes on virtual columns in MariaDB 5.2

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table x(a int not null primary key,
    ->    b varchar(32),
    ->    c int as (a mod 5) virtual,
    ->    d int as (a mod 7) persistent
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> describe x;
+-------+-------------+------+-----+---------+---------+
| Field | Type        | Null | Key | Default | Extra   |
+-------+-------------+------+-----+---------+---------+
| a     | int(11)     | NO   | PRI | NULL    |         |
| b     | varchar(32) | YES  |     | NULL    |         |
| c     | int(11)     | YES  |     | NULL    | VIRTUAL |
| d     | int(11)     | YES  |     | NULL    | VIRTUAL |
+-------+-------------+------+-----+---------+---------+
4 rows in set (0.03 sec)

MariaDB [test]> insert into x(a,b) values(1,1),(20,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from x;
+----+------+------+------+
| a  | b    | c    | d    |
+----+------+------+------+
|  1 | 1    |    1 |    1 |
| 20 | 20   |    0 |    6 |
+----+------+------+------+
2 rows in set (0.00 sec)

MariaDB [test]> create index c on x(c);
ERROR 1645 (HY000): Key/Index cannot be defined on a non-stored computed column
MariaDB [test]> create index d on x(d);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> explain select * from x where d = 6;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | x     | ref  | c             | c    | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)