(Find all correct answers)
 

a) By using several engines in a db, you can combine their
   features and advantages.
b) The more engines you use, the more you can do in parallel.
c) Tables differ in size, amount of reads/writes etc and
   therefore have different storage-engine requirements.
d) Certain features are only are only available with
   certain storage engines.

 
[ Basic Optimizations (10%) - Choosing Appropriate Storage Engines ]

(Find all correct answers)
 
a) Crash-safe tables
b) Full-text search
c) Foreign keys
d) Transactions
 
[ Basic Optimizations (10%) - Choosing Appropriate Storage Engines ]

 
(Find all correct answers)
 
a) Summary tables
b) Concurrency
c) Small foot-print on disk
d) No transactions
 
[ Basic Optimizations (10%) - Choosing Appropriate Storage Engines ]

 
Connect the storage engines a-d with their features 1-4
 
a) InnoDB
b) MyISAM
c) BLACKHOLE
d) MEMORY
 
1) Very fast and supports hash indexes
2) Does not store anything – can be useful for example in some replication scenarios
3) Transactions and row-level locking
4) Fast and is MySQL’s default (in 5.0)
 
[ Basic Optimizations (10%) - Choosing Appropriate Storage Engines ]

 
(Find all correct answers)
 
a) VsAM
b) BLACKHOLE
c) InnoDB
d) MyISAM
e) MRG_MYISAM
f) REDDWARF
 
Extra question: How do you check which engines are available in your environment?
 
[ Basic Optimizations (10%) - Choosing Appropriate Storage Engines ]

1. select now(),database() from dual;
2. select now(),database();

 
(Select all correct answers)
 
a) Query 2 is invalid.
b) Query 1 is valid only, if a table called dual exists.
c) Query 1 and 2 are valid and do exactly the same.
d) Dual is a dummy table-reference and acts like a table with 1 row.

(Find all correct answers)

create view tt.inno_tables as
  select v1.* from
    (select * from information_schema.tables
    where engine='innodb')
  v1;

 
a) This view is valid and mysql can use the MERGE algorithm.
b) This view is valid and mysql has to use the TEMPTABLE algorithm.
c) This view is invalid because it uses a select in the FROM-clause.
d) This view is invalid, as you can’t create a view on information_schema tables.

(Find all)

+--------------------+--------+-----------+-----------+--------+-----------+
| table_schema       | tables | myisam_tb | innodb_tb | mem_tb | dbsize_mb |
+--------------------+--------+-----------+-----------+--------+-----------+
| information_schema |     17 |         4 |         0 |     13 |         0 |
| mysql              |     17 |        17 |         0 |      0 |         0 |
| test               |      4 |         4 |         0 |      0 |         0 |
| tt                 |     49 |        45 |         3 |      0 |        53 |
| tt2                |     10 |        10 |         0 |      0 |         0 |
+--------------------+--------+-----------+-----------+--------+-----------+
5 rows in set (0.14 sec)

a)
select table_schema,count(*) as tables,
  sum(if(engine='myisam',1,0)) as myisam_tb,
  sum(if(engine='innodb',1,0)) as innodb_tb,
  sum(if(engine='memory',1,0)) as mem_tb,
  floor((sum(index_length)+sum(data_length))/1024/1024) as dbsize_mb
from information_schema.tables group by table_schema;

b)
select table_schema,engine,count(*) as tables,
  floor((sum(index_length)+sum(data_length))/1024/1024) as dbsize_mb
from information_schema.tables group by table_schema,engine;

c)
select v1.table_schema,
  ifnull(v1.tables,0) as tables,ifnull(v2.myisam_tb,0) as myisam_tb,
  ifnull(v3.innodb_tb,0) as innodb_tb,ifnull(v4.mem_tb,0) as mem_tb,
  ifnull(v1.dbsize_mb,0) as dbsize_mb
from (select table_schema,count(*) as tables,
  floor((sum(index_length)+sum(data_length))/1024/1024) as dbsize_mb
  from information_schema.tables group by table_schema) v1
left join (select table_schema,count(*) as myisam_tb
  from information_schema.tables
  where engine='myisam' group by table_schema) v2
on (v1.table_schema=v2.table_schema)
left join (select table_schema,count(*) as innodb_tb
  from information_schema.tables
  where engine='innodb' group by table_schema) v3
on (v1.table_schema=v3.table_schema)
left join (select table_schema,count(*) as mem_tb
  from information_schema.tables
  where engine='memory' group by table_schema) v4
on (v1.table_schema=v4.table_schema) ;
 

 
[ Obtaining Database Metadata (10%) - Using INFORMATION_SCHEMA to Obtain Metadata ]