Exercise 7

1.

Z:\FIRMA>mysql firma -u root -p < "luontiskripti2.sql"
Enter password: ****
ERROR 1506 (HY000) at line 27: Foreign key clause is not yet supported in conjun
ction with partitioning

2.

mysql> SELECT etunimi, sukunimi, palkka FROM tyontekija WHERE palkka > 3500;


3.

Without partitions.

Z:\public_html\Sql>mysqlslap --user=root --password --concurrency=5 --iterations
=10 --create-schema=firma --query=slap.sql
Enter password: ****
Benchmark
        Average number of seconds to run all queries: 2.000 seconds
        Minimum number of seconds to run all queries: 1.953 seconds
        Maximum number of seconds to run all queries: 2.047 seconds
        Number of clients running queries: 5
        Average number of queries per client: 1

With partitioning.

Z:\public_html\Sql>mysqlslap --user=root --password --concurrency=5 --iterations
=10 --create-schema=firma --query=slap.sql
Enter password: ****
Benchmark
        Average number of seconds to run all queries: 1.573 seconds
        Minimum number of seconds to run all queries: 0.765 seconds
        Maximum number of seconds to run all queries: 1.797 seconds
        Number of clients running queries: 5
        Average number of queries per client: 1

With indexing and partitioning.

Average was around 3.323 seconds, when we used FORCE command. It would not use indexing otherwise, because it was slower.

4.

mysql> ALTER TABLE Tyontekija DROP PARTITION p0;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO Tyontekija VALUES (2555555, 'Vainikka', 'Joona', 2, 'Metropol
ia', '00720', '0407534488', 1500)
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tyontekija WHERE sukunimi='Vainikka';
+---------+----------+---------+--------+------------+---------+------------+---
-----+
| Numero  | Sukunimi | Etunimi | Osasto | Lahios     | Postino | Puhelin    | Pa
lkka |
+---------+----------+---------+--------+------------+---------+------------+---
-----+
| 2555555 | Vainikka | Joona   |      2 | Metropolia | 00720   | 0407534488 |
1500 |
+---------+----------+---------+--------+------------+---------+------------+---
-----+
1 row in set (0.17 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM tyontekija WHERE palkka=1500;
+----+-------------+------------+------------+------+---------------+------+----
-----+------+--------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key
_len | ref  | rows   | Extra       |
+----+-------------+------------+------------+------+---------------+------+----
-----+------+--------+-------------+
|  1 | SIMPLE      | tyontekija | p1         | ALL  | NULL          | NULL | NUL
L    | NULL | 180820 | Using where |
+----+-------------+------------+------------+------+---------------+------+----
-----+------+--------+-------------+
1 row in set (0.00 sec)

As seen above I seem to go to partition1 with my 1500 euro salary.
Because partition1 is for salaries less than 3000 euros.

5.

Deleting a partition also deletes the information associated with that partition, allowing fast removal of information.

6.

For example a log file, old logs are moved to partition0, and it is deleted at certain intervals while newer log files are kept in partition1.

7.