Imagine you get the logs of a vending-machine.
You want to know the average time between vending-events.
The log table looks like this:

DROP TABLE IF EXISTS vending_log;
CREATE TABLE `vending_log` (
  `sold_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
   ON UPDATE CURRENT_TIMESTAMP,
  `product_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sold_ts`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- this fills test-data
set @a:='2010-01-01 12:00:00';
insert into vending_log
  select @a:=adddate(@a,interval 1 + rand()*2500 second),
    floor(rand()*100)
  from information_schema.tables v1,
       information_schema.tables v2 limit 1000;

How do you calculate the average time between the vending-events?
 
[Just for fun]

You want to list all rows from the mysql.help_keyword table,
in which the name contains ‘TREE’ or ‘TYPE’.
The obvious choice is:

SELECT * FROM mysql.help_keyword WHERE name like '%TREE%' or name like '%TYPE%';
+-----------------+-------+
| help_keyword_id | name  |
+-----------------+-------+
|              87 | RTREE |
|             107 | TYPE  |
|             441 | BTREE |
|             445 | TYPES |
+-----------------+-------+
4 rows in set (0.01 sec)

What other alternatives can you think of?

[Just for fun]

Again, we’ll create and then break a table. Follow the instructions below:
 

drop table if exists t1;
create table t1 (tid bigint not null auto_increment,
                 ttype int,
                 tsum double,
                 tdate date,
                 primary key (tid),index(ttype,tdate))
engine=myisam;
insert into t1 select null,rand()*99999999,floor(rand()*3),
         adddate('2010-01-01',interval rand()*360 day)
  from information_schema.columns,
       information_schema.tables limit 40000 ;
flush tables;

select concat(@@global.datadir,database()) as dbdir;

While keeping the mysql session open, go to the dbdir directory and delete t1.MYI .
In the mysql session, issue a “check table t1;”
you should see something like this:

MariaDB [tt]> check table t1;
+-------+-------+----------+----------------------------------+
| Table | Op    | Msg_type | Msg_text                         |
+-------+-------+----------+----------------------------------+
| tt.t1 | check | Error    | Can't find file: 't1' (errno: 2) |
| tt.t1 | check | status   | Operation failed                 |
+-------+-------+----------+----------------------------------+
2 rows in set (0.00 sec)

 
Note, that a “REPAIR TABLE t1;” will not repair the table this time (try it!).
Pick from the options below everything that needs to be done in order to repair this table.

a) mysqladmin -p -u root shutdown
b) repair table t1 index_rebuild;
c) repair table t1 use_frm;
d) repair table t1 extended;
e) repair table t1 quick;

 
[MyISAM table maintenance - Investigate and repair broken tables]

You want to copy a MyIsam table the fasted way possible from database qotd1 to qotd2 inside the same instance.
Check the scenario below:

create database qotdt1;
create database qotdt2;

use qotdt1;
set @a:=0;
create table qt1 (primary key (tid),index(ttype,tdate))
  engine=myisam
  select @a:=@a+1 as tid,rand()*99999999 as tsum, floor(rand()*3) as ttype,
         adddate('2010-01-01',interval rand()*360 day) as tdate
  from information_schema.columns, information_schema.tables limit 100000 ;

select @@global.datadir;

You decide, that using operating system means will be the fastest way.
Which of the procedures below will make a consistent copy?
(Find all correct answers)
 

a) FLUSH TABLES WITH READ LOCK;
   Copy [datadir]/qotdt1/qt1.* to [datadir]/qotdt2/
   UNLOCK TABLES;
   FLUSH TABLES;

b) Shutdown the instance.
   Copy [datadir]/qotdt1/qt1.* to [datadir]/qotdt2/
   Startup the instance.

c) LOCK TABLE qotdt1.qt1 READ;
   FLUSH TABLES;
   Copy [datadir]/qotdt1/qt1.* to [datadir]/qotdt2/
   UNLOCK TABLES;
   FLUSH TABLES;

 
[MyISAM table maintenance - Backup]

Now we create and break a table:

drop table if exists t1;
set @a:=0;
create table t1 (primary key (tid),index(ttype,tdate))
  engine=myisam
  select @a:=@a+1 as tid,rand()*99999999 as tsum, floor(rand()*3) as ttype,
         adddate('2010-01-01',interval rand()*360 day) as tdate
  from information_schema.columns, information_schema.tables limit 40000 ;
create table if not exists break_it like t1;
flush tables;

select concat(@@global.datadir,database()) as dbdir;

 
While keeping the mysql session open, go to the dbdir directory and delete t1.MYI .
Then copy break_it.MYI to t1.MYI .
In the mysql session, issue a “check table t1;”

you should see something like this:

MariaDB [tt]> check table t1;
+-------+-------+----------+-------------------------------------------------+
| Table | Op    | Msg_type | Msg_text                                        |
+-------+-------+----------+-------------------------------------------------+
| tt.t1 | check | warning  | Table is marked as crashed                      |
| tt.t1 | check | warning  | Size of datafile is: 1693848       Should be: 0 |
| tt.t1 | check | error    | Record-count is not ok; is 45360   Should be: 0 |
| tt.t1 | check | warning  | Found 45360 key parts. Should be: 0             |
| tt.t1 | check | error    | Corrupt                                         |
+-------+-------+----------+-------------------------------------------------+
5 rows in set (0.12 sec)

How do you repair it again?

a) fix table t1;
b) alter table t1 engine myisam;
c) repair table t1;
d) optimize table t1;

 
[MyISAM table maintenance - Investigate and repair broken tables]

select a b from (select 1 a) b, (select 1 b union select 2) a;

a)
+---+
| a |
+---+
| 1 |
| 1 |
+---+

b)
+---+
| b |
+---+
| 1 |
| 1 |
+---+

c)
+---+
| b |
+---+
| 2 |
| 2 |
+---+

d)
+---+
| b |
+---+
| 1 |
+---+

[Just for fun]

Let’s stick with the scenario from yesterday:

CREATE TABLE `bla_di_da` (
`bla` int(11) DEFAULT NULL,
`di` int(11) DEFAULT NULL,
`da` int(11) DEFAULT NULL,
KEY `di` (`di`),
KEY `di_2` (`di`),
KEY `di_3` (`di`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Let’s pretend the table contains a lot of rows.
(You could fill it with the row-generator technique, which was used in many questions)
 
Now, you’d like to get rid of di_2 and di_3. What is the fastest way?

1.: alter table la_di_da drop key di_2, drop key di_3;
2.: alter table la_di_da drop key di_2;
    alter table la_di_da drop key di_3;

 
a) 1 and 2 take the same amount of time
b) 1 ist faster
c) 2 is faster
 
[just for fun]

create table bla_di_da(bla int,di int, da int);
alter table bla_di_da add index (di);
alter table bla_di_da add index (di);
alter table bla_di_da add index (di);

a) The 2nd and 3rd ALTERs will fail
b) All the ALTERs will run fine and 3 indexes will be the result
c) All the ALTERs will run fine and 1 index will be the result

 
[ Just for fun ]