How FriendFeed uses MySQL to store schema-less data

http://backchannel.org/blog/friendfeed-schemaless-mysql

PHP APC test

I did some tests on APC (alternative PHP cache).
Here are the results:
http://nmmm.nu/apc.htm

Dynamic columns as a bridge between SQL and NoSQL

http://monty-says.blogspot.com/2011/05/dynamic-columns-as-bridge-between-sql.html

O'Reilly MySQL CE 2010: Slava Akhmechet and Michael Glukhovsky, "RethinkDB: ..."

NoSQL

O'Reilly MySQL CE 2010: Brian Aker, "A Guide to No-SQL"


...and...

O'Reilly MySQL CE 2010: Mark Atwood, "Guide to NoSQL, redux"

O'Reilly MySQL CE 2010: Brian Aker, "State of Drizzle"

O'Reilly MySQL CE 2011: Michael Widenius, "State of MariaDB"

Performance Tuning Best Practices for MySQL

ZeroMQ is the Answer

ZeroMQ is the Answer - Ian Barber from PHP UK Conference on Vimeo.

Large Scale Data Processing with Hadoop and PHP

Large Scale Data Processing with Hadoop and PHP - David Zuelke from PHP UK Conference on Vimeo.

NoSQL Databases: What, Why and When

NoSQL Databases: What, Why and When - Lorenzo Alberton from PHP UK Conference on Vimeo.

The InnoDB Storage Engine for MySQL

The InnoDB Storage Engine for MySQL - Morgan Tocker from PHP UK Conference on Vimeo.

MariaDB -- drop in replacement of MySQL - Video

MariaDB -- drop in replacement of MySQL
Nikolay Mihaylov
Openfest 20.NOV.2010

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)

MariaDB - drop in replacement of MySQL

MariaDB - drop in replacement of MySQL
I will speak at OpenFest conferention on 20.Nov.2010 15:00 , Sofia, Interpred, zala Plovdiv.
Map and additional info here:
http://openfest.org/archive/openfest-2010/program/

Monty Widenius on MariaDB

Strange PBXT issue

MariaDB [test]> create table b (a int primary key) engine=pbxt;
Query OK, 0 rows affected (3 min 43.59 sec)


MariaDB [test]> insert into b values(1);
Query OK, 1 row affected (8.08 sec)


MariaDB [test]> insert into b values(2);
Query OK, 1 row affected (8.08 sec)


MariaDB [test]> insert into b values(3);
Query OK, 1 row affected (1 min 21.43 sec)


MariaDB [test]> select * from b;
+---+
| a |
+---+
| 1 |
| 2 |    Hmmm? where is 3?
+---+
2 rows in set (0.00 sec)


MariaDB [test]> insert into b values(3);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'


MariaDB [test]> select * from b where a  = 3;
+---+
| a |
+---+
| 3 |
+---+
1 row in set (0.10 sec)


MariaDB [test]> select * from b order by a;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)