Wednesday, May 21, 2014

MySQL Magic Square 4x4 : Routines, Loop , Variables

I got sidetracked today playing with Magic Squares and thought it might be a good chance to give an example of using MySQL Routines , Loops and IF checks.

So If you are unaware of what a Magic Square is I have included a few links. It might save you a Google search but otherwise think Sudoku as an example.
Again this is more of an example of how to use Routines & Loops and an IF Check, so I have limited the magic square to a 4x4 square.
I am passing the value of 4 to the routine to show how to do so. It is an unnecessary step since I am hard coding the  square to 4 anyway but it works for the example.

DROP PROCEDURE IF EXISTS magic_sq;
DROP TABLE IF EXISTS `magic_sq` ;

delimiter //
CREATE PROCEDURE magic_sq( N int(11))
    BEGIN
DECLARE nXn  INT;
DECLARE SQ_SUM  INT;
DECLARE _passfail_  INT;
DECLARE min  INT;
DECLARE max  INT;

-- DRAW THE TEMPLATE FOR THE SQUARE MAX of 8 FOR NOW
DROP TABLE IF EXISTS `magic_sq` ;
CREATE TABLE `magic_sq` (
`xy_id` int(11) NOT NULL AUTO_INCREMENT, 
`1` int(11) NULL DEFAULT NULL,
`2` int(11) NULL DEFAULT NULL,
`3` int(11) NULL DEFAULT NULL,
`4` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`xy_id`) ,
 UNIQUE KEY `Y1` (`1`),
 UNIQUE KEY `Y2` (`2`),
 UNIQUE KEY `Y3` (`3`),
 UNIQUE KEY `Y4` (`4`)
) ENGINE= MEMORY;


-- n X n
SET @nXn = N + N;
-- SQ_SUM This is the formula for what the total should equal
SET @SQ_SUM =  ( N * (POW(N,2) + 1) ) / 2;
-- MIN Value
SET @min=1;
-- MAX Value
SET @max=POW(N,2);

-- BUILD THE SQUARE
WHILE ( @min <= @nXn ) 
DO

-- TEST VALUES
SET _passfail_ = IF ( (@min + (@min +1) + ( @max - 1) +@max) = @SQ_SUM ,1 , 0 )  ;

-- IF VALID RESULTS THEN SAVE THEM
 IF _passfail_ = 1 THEN
      INSERT INTO magic_sq  VALUES (NULL,@min ,(@min +1),( @max - 1) , @max );
 END IF;

-- CONTINUE
SET @min= @min +2;
SET @max= @max -2;

END WHILE;

    END//
delimiter ;


Now that I built out the routine,  do I get valid results?  Keep in mind this is not ALL of the possible options, just options that might work. 924 options for a 4x4 square exist.

CALL magic_sq(4);
 select * from magic_sq;
+-------+------+------+------+------+
| xy_id | 1    | 2    | 3    | 4    |
+-------+------+------+------+------+
|     1 |    1 |    2 |   15 |   16 |
|     2 |    3 |    4 |   13 |   14 |
|     3 |    5 |    6 |   11 |   12 |
|     4 |    7 |    8 |    9 |   10 |
+-------+------+------+------+------+


+-------+---------+
| xy_id | per_row |
+-------+---------+
|     1 |      34 |
|     2 |      34 |
|     3 |      34 |
|     4 |      34 |
+-------+---------+

No!  They work for the rows but not the columns.  While this shows how to use routines, loops and an IF statement, the example fails it it doesn't provide what I wanted.  So I had to rework the insert with more of an IF check as well as swap a few numbers at the end.

DROP PROCEDURE IF EXISTS magic_sq;
DROP TABLE IF EXISTS `magic_sq` ;

delimiter //
CREATE PROCEDURE magic_sq( N int(11))
    BEGIN
DECLARE nXn  INT;
DECLARE SQ_SUM  INT;
DECLARE _passfail_  INT;
DECLARE _io_  INT;
DECLARE min  INT;
DECLARE max  INT;

-- DRAW THE TEMPLATE FOR THE SQUARE MAX of 8 FOR NOW
DROP TABLE IF EXISTS `magic_sq` ;
CREATE TABLE `magic_sq` (
`xy_id` int(11) NOT NULL AUTO_INCREMENT,
`1` int(11) NULL DEFAULT NULL,
`2` int(11) NULL DEFAULT NULL,
`3` int(11) NULL DEFAULT NULL,
`4` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`xy_id`) ,
 UNIQUE KEY `Y1` (`1`),
 UNIQUE KEY `Y2` (`2`),
 UNIQUE KEY `Y3` (`3`),
 UNIQUE KEY `Y4` (`4`)
) ENGINE= MEMORY;


-- n X n
SET @nXn = N + N;
-- SQ_SUM This is the formula for what the total should equal
SET @SQ_SUM =  ( N * (POW(N,2) + 1) ) / 2;
-- MIN Value
SET @min=1;
-- MAX Value
SET @max=POW(N,2);

-- insert_options
SET _io_ =0;

-- BUILD THE SQUARE
WHILE ( @min <= @nXn )
DO

-- TEST VALUES
SET _passfail_ = IF ( (@min + (@min +1) + ( @max - 1) +@max) = @SQ_SUM ,1 , 0 )  ;

-- IF VALID RESULTS THEN SAVE THEM
 IF _passfail_ = 1 THEN

  IF _io_ = 0 THEN
      INSERT INTO magic_sq  VALUES (NULL,@min ,(@min +1),( @max - 1) , @max );
      SET _io_ =1;
  ELSEIF _io_ = 1 THEN
      INSERT INTO magic_sq  VALUES (NULL,( @max - 1),@max , @min , (@min +1) );
      SET _io_ =2;
  ELSEIF _io_ = 2 THEN
      INSERT INTO magic_sq  VALUES (NULL,@max ,(@min +1) , ( @max - 1) , @min );
      SET _io_ =4;
    ELSEIF _io_ = 4 THEN
      INSERT INTO magic_sq  VALUES (NULL, (@min +1) , @max    , @min ,( @max - 1) );
      SET _io_ =0;
  END IF;

 END IF;

-- CONTINUE
SET @min= @min +2;
SET @max= @max -2;

END WHILE;
SELECT @x3y2 := `2` FROM magic_sq WHERE xy_id = 3;
SELECT @x3y3 := `3` FROM magic_sq WHERE xy_id = 3;
SELECT @x4y2 := `2` FROM magic_sq WHERE xy_id = 4;
SELECT @x4y3 := `3` FROM magic_sq WHERE xy_id = 4;


UPDATE magic_sq SET `2` = @x4y3  , `3` = @x4y2 WHERE xy_id = 3;
UPDATE magic_sq SET `2` = @x3y3   , `3` = @x3y2 WHERE xy_id = 4;
select * from magic_sq;
select SUM(`1`),SUM(`2`),SUM(`3`),SUM(`4`) from magic_sq;
select xy_id, SUM(`1` +`2` +`3` + `4`) as per_row from magic_sq GROUP BY xy_id;

    END//
delimiter ;


Now does this work?

CALL magic_sq(4);
+-------+------+------+------+------+
| xy_id | 1    | 2    | 3    | 4    |
+-------+------+------+------+------+
|     1 |    1 |    2 |   15 |   16 |
|     2 |   13 |   14 |    3 |    4 |
|     3 |   12 |    7 |   10 |    5 |
|     4 |    8 |   11 |    6 |    9 |
+-------+------+------+------+------+
4 rows in set (0.22 sec)

+----------+----------+----------+----------+
| SUM(`1`) | SUM(`2`) | SUM(`3`) | SUM(`4`) |
+----------+----------+----------+----------+
|       34 |       34 |       34 |       34 |
+----------+----------+----------+----------+
1 row in set (0.22 sec)

+-------+---------+
| xy_id | per_row |
+-------+---------+
|     1 |      34 |
|     2 |      34 |
|     3 |      34 |
|     4 |      34 |
+-------+---------+
 
OK I cheated a little by just moving the columns around but you get the idea.

Wednesday, May 14, 2014

A look at MySQL 5.7 DMR

So I figured it was about time I looked at MySQL 5.7. This is a high level overview, but I was looking over the MySQL 5.7 in a nutshell document:
So I am starting with a fresh Fedora 20 (Xfce) install.
Overall, I will review a few items that I found curious and interesting with MySQL 5.7. The nutshell has a lot of information so well worth a review.

I downloaded the MySQL-5.7.4-m14-1.linux_glibc2.5.x86_64.rpm-bundle.tar

The install was planned on doing the following
# tar -vxf MySQL-5.7.4-m14-1.linux_glibc2.5.x86_64.rpm-bundle.tar
# rm -f mysql-community-embedded*
]# ls -a MySQL-*.rpm
MySQL-client-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-embedded-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-shared-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-devel-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-server-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-test-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
# yum -y install MySQL-*.rpm
Complete!

While it said Complete I also noticed an error. It should have not finished the install if it found an error but ok....
FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db:
Data::Dumper

This error was confirmed .. 
# /etc/init.d/mysql start
Starting MySQL............ ERROR! The server quit without updating PID file
# tail /var/lib/mysql/fedora20mysql57.localdomain.err
ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
# /usr/bin/mysql_install_db
FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db:
Data::Dumper
# yum -y install perl-Data-Dumper
# /usr/bin/mysql_install_db
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
# chown -R mysql:mysql /var/lib/mysql/mysql/
# cat /root/.mysql_secret
# mysql -u root -p
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('somepassword');
Query OK, 0 rows affected (0.01 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.4-m14 |
+-----------+

A more robust process for upgrades and etc is documented here:
http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html
Check to ensure you have GLIBC_2.15 if you plan to install this on your OS.

OK so now that it is installed, what do we have.
mysql> select User , Host,plugin from mysql.user \G
*************************** 1. row ***************************
  User: root
  Host: localhost
plugin: mysql_native_password
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
mysql> SELECT @@default_password_lifetime \G
*************************** 1. row ***************************
@@default_password_lifetime: 360

These are all long overdue improvements, and thank you all for the improvements.
So now to look over the rest, we at least want some kind of data and schema. So I will install the world database for the tests. 
# wget http://downloads.mysql.com/docs/world_innodb.sql.gz
# gzip -d world_innodb.sql.gz
# mysql -u root -p -e "create database world";
# mysql -u root -p world < world_innodb.sql
# mysql -u root -p world
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB

mysql> ALTER TABLE City ALGORITHM=INPLACE, RENAME KEY CountryCode TO THECountryCode;
Query OK

mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `THECountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB


mysql> DROP TABLE test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'
mysql> GET DIAGNOSTICS CONDITION 1  @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
Query OK, 0 rows affected (0.45 sec)

mysql> SELECT @p1, @p2 \G
*************************** 1. row ***************************
@p1: 42S02
@p2: Unknown table 'test.no_such_table'
1 row in set (0.01 sec)
  • Triggers
    The trigger limitation has been lifted and multiple triggers are permitted. Please see the documentation as they give a good example. I will demo it some here just to show that multiple triggers on a single table are possible.
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+

mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
    ->    FOR EACH ROW PRECEDES ins_sum
    ->    SET
    ->    @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
    ->    @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);

mysql> SHOW triggers \G
*************************** 1. row ***************************
             Trigger: ins_transaction
               Event: INSERT
               Table: account
           Statement: SET
   @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
   @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0)
              Timing: BEFORE
             Created: 2014-05-14 21:23:49.66
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: ins_sum
               Event: INSERT
               Table: account
           Statement: SET @sum = @sum + NEW.amount
              Timing: BEFORE
             Created: 2014-05-14 21:22:47.91
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
mysql> CREATE TABLE t1
    -> (  c1 CHAR(10) CHARACTER SET latin1
    -> ) DEFAULT CHARACTER SET gb18030 COLLATE gb18030_chinese_ci;
Query OK
mysql> HANDLER City OPEN AS city_handle;
mysql> HANDLER city_handle READ FIRST;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+

mysql> HANDLER city_handle READ NEXT LIMIT 3;
+----+-----------+-------------+---------------+------------+
| ID | Name      | CountryCode | District      | Population |
+----+-----------+-------------+---------------+------------+
|  5 | Amsterdam | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag      | NLD         | Zuid-Holland  |     440900 |
+----+-----------+-------------+---------------+------------+

mysql> CREATE TABLE `t2` (
    ->   `t2_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `inserttimestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   `somevalue` int(10) unsigned DEFAULT NULL,
    ->   `rowLastUpdateTime` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`t2_id`,`inserttimestamp`)
    -> ) ENGINE=InnoDB;

mysql> ALTER TABLE t2
    ->  PARTITION BY RANGE ( TO_DAYS(inserttimestamp) ) (
    ->      PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
    ->      PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
    ->      PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
    ->      PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
    ->      PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
    ->      PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
    ->      PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
    ->      PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
    ->      PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
    ->      PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
    ->      PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
    ->      PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
    ->      PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
    ->  );

mysql> INSERT INTO t2 VALUES (NULL,NOW(),1,NOW());
mysql> HANDLER t2 OPEN AS t_handle;
mysql> HANDLER t_handle READ FIRST;
+-------+---------------------+-----------+---------------------+
| t2_id | inserttimestamp     | somevalue | rowLastUpdateTime   |
+-------+---------------------+-----------+---------------------+
|     1 | 2014-05-14 21:53:28 |         1 | 2014-05-14 21:53:28 |
+-------+---------------------+-----------+---------------------+
mysql> select @@binlog_format\G
*************************** 1. row ***************************
@@binlog_format: ROW


# mysqlbinlog --database=world  mysql-bin.000002 | grep world | wc -l
22543# mysqlbinlog --rewrite-db='world->renameddb'  mysql-bin.000002 | grep renameddb | wc -l
22542

Monday, May 5, 2014

MySQL ERROR 1118 (42000) MySQL 5.0 to MySQL 5.5 or above

So I recently had a TBs database that that I had to upgrade from MySQL 5.0 to MySQL 5.5.
This blog post will touch on the following:
  • sql_mode 
  • innodb_strict_mode 
  • SLAVE IO_THREAD 
During the mysql_upgrade process (which does a mysqlcheck) I quickly noticed the following error:

ERROR 1118 (42000) at line 23: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

So I started I had to check a few things first.

# The master DB was > select @@sql_mode; 
+------------+ 
| @@sql_mode | 
+------------+ 
| | 
+------------+
New DB was
mysql> select @@sql_mode; 
+--------------------------------------------+ 
| @@sql_mode | 
+--------------------------------------------+ 
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+ 
OK I would rather have something for sql_mode over a empty value.
 So I let that go.
# MASTER SERVER 
select @@innodb_strict_mode; 
ERROR 1193 (HY000): Unknown system variable 'innodb_strict_mode' 

# NEW SERVER mysql> select @@innodb_strict_mode; 
+----------------------+ 
| @@innodb_strict_mode | 
+----------------------+ 
| 1 | 
+----------------------+
You can read more about this setting here:
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-strict-mode.html
mysql> SET GLOBAL innodb_strict_mode=0;

The above command allowed me to at least mysql upgrade an check the tables. To be safe I still set up a bash script to dump and load all of the tables. (yes all the TBS of them)

 Do not take a short cut and assume all is ok.

If you are getting errors mysqldump and reload the files. Better to be safe than sorry later.

 Once the data is loaded into 5.5+ I can review and adjust variables.

So needless to say it is going to take some time to dump and load TBs of data. I want to give the slave all the opportunity I can to catch up as fast as possible. While my shell scripts are dumping and loading the data, there is no reason the database cannot be gathering the binlogs in the meantime.

mysql> START SLAVE IO_THREAD ; 
So now while my processlist will show:

SELECT /*!40001 SQL_NO_CACHE */ * 
the slave status shows:
Slave_IO_Running: Yes
 Slave_SQL_Running: No

So I am gathering logs while I clean the database.
This should allow the database to catch up quickly once I am ready to go.

Friday, May 2, 2014

MySQL Benchmark with mysqlslap

So benchmarking different MySQL queries against your database is a wise thing to do. That should go without saying. While we optimize queries the best we can using EXPLAIN (and EXPLAIN EXTENDED)  taking them time to benchmark them should prove helpful.

This is a simple example of executing a mysqlslap statement.

For this example I loaded the WORLD database from MySQL. ( http://dev.mysql.com/doc/index-other.html )

I created a query that joined all three tables  and put it into /tmp/tests.sql. The explain plan is below.

root@localhost [world]> EXPLAIN EXTENDED SELECT C.Name as City, Y.Name as Country, L.Language,Y.Population FROM City C INNER JOIN Country Y ON C.CountryCode = Y.Code INNER JOIN CountryLanguage L ON C.CountryCode = L.CountryCode WHERE C.Name LIKE 'D%' AND Y.Continent='Europe' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: C
         type: range
possible_keys: CountryCode,name_key
          key: name_key
      key_len: 5
          ref: NULL
         rows: 127
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Y
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: world.C.CountryCode
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: L
         type: ref
possible_keys: PRIMARY,CountryCode
          key: CountryCode
      key_len: 3
          ref: world.C.CountryCode
         rows: 2
     filtered: 100.00
        Extra: Using index
3 rows in set, 1 warning (0.00 sec)

root@localhost [world]> show warnings \G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `world`.`C`.`Name` AS `City`,`world`.`Y`.`Name` AS `Country`,`world`.`L`.`Language` AS `Language`,`world`.`Y`.`Population` AS `Population` from `world`.`City` `C` join `world`.`Country` `Y` join `world`.`CountryLanguage` `L` where ((`world`.`Y`.`Code` = `world`.`C`.`CountryCode`) and (`world`.`L`.`CountryCode` = `world`.`C`.`CountryCode`) and (`world`.`Y`.`Continent` = 'Europe') and (`world`.`C`.`Name` like 'D%'))


Now the mysqlslap tool has been around since MySQL 5.1.4
Below are some other helpful links.
Now that I have my query, I can benchmark it against the database with the following command.

mysqlslap --concurrency=150 --iterations=50 --query=/tmp/test.sql --create-schema=world

One note:
The query has to be very clean as the tool does error easily.
For example the following tossed this error:

SELECT C.Name as City, Y.Name as Country, L.Language,Y.Population
FROM City C
INNER JOIN Country Y ON C.CountryCode = Y.Code
INNER JOIN CountryLanguage L ON C.CountryCode = L.CountryCode
WHERE C.Name LIKE 'D%' AND Y.Continent='Europe'

While this query worked just fine.

SELECT C.Name as City, Y.Name as Country, L.Language,Y.Population FROM City C INNER JOIN Country Y ON C.CountryCode = Y.Code INNER JOIN CountryLanguage L ON C.CountryCode = L.CountryCode WHERE C.Name LIKE 'D%' AND Y.Continent='Europe'


The tool will output benchmark results for you


Benchmark
    Average number of seconds to run all queries: 0.104 seconds
    Minimum number of seconds to run all queries: 0.096 seconds
    Maximum number of seconds to run all queries: 0.141 seconds
    Number of clients running queries: 150
    Average number of queries per client: 1


mysqlslap --help will give you numerous options to test your queries with.

You can do everything automatically

# mysqlslap --auto-generate-sql
Benchmark
    Average number of seconds to run all queries: 0.243 seconds
    Minimum number of seconds to run all queries: 0.243 seconds
    Maximum number of seconds to run all queries: 0.243 seconds
    Number of clients running queries: 1
    Average number of queries per client: 0


You can test inserts as well. For example I created this table:

CREATE TABLE `foobar_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ;


So then tested with the following.

 # mysqlslap --concurrency=1150 --iterations=530  --query="use test;insert into foobar_table (id) values (null)" --delimiter=";"
mysqlslap: Could not create thread


OK Errors are not very helpful... But hopefully you notice the error. It is hard to have 1150 concurrent transactions if you only have 530 iterations.


# mysqlslap --concurrency=150 --iterations=30  --query=/tmp/test1.sql --create-schema=test --verbose
Benchmark
    Average number of seconds to run all queries: 0.260 seconds
    Minimum number of seconds to run all queries: 0.192 seconds
    Maximum number of seconds to run all queries: 0.476 seconds
    Number of clients running queries: 150
    Average number of queries per client: 1


For example the following worked much better.

# mysqlslap --concurrency=200 --iterations=1000  --query=" insert into foobar_table (id) values (null)"  --verbose --create-schema=test
Benchmark
    Average number of seconds to run all queries: 0.282 seconds
    Minimum number of seconds to run all queries: 0.217 seconds
    Maximum number of seconds to run all queries: 0.726 seconds
    Number of clients running queries: 200
    Average number of queries per client: 1


Just to prove that we are doing real inserts..

 root@localhost [test]> select count(id) from foobar_table;
+-----------+
| count(id) |
+-----------+
|    206091 |
+-----------+
1 row in set (0.13 sec)

Now I also should say that this is just a test database that I use for blog posts, so do not evaluate your production databases against these results.

I guess the point after all this... find a troublesome query, optimize it the best you can, and benchmark it. It is better to know your limits instead of just a guess.