# MySQL performance test results using employee database
**URL:** https://forums.percona.com/t/mysql-performance-test-results-using-employee-database/2632
**Category:** Other MySQL® Questions
**Created:** [April 16, 2013, 6:59am UTC](https://forums.percona.com/t/mysql-performance-test-results-using-employee-database/2632 "2013-04-16T06:59:40Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@keeperhood](https://forums.percona.com/u/keeperhood)
#### Post date: [April 16, 2013, 6:59am UTC](https://forums.percona.com/t/mysql-performance-test-results-using-employee-database/2632/1 "2013-04-16T06:59:40Z")
I’ve used the http://[MySQL :: Employees Sample Database](http://dev.mysql.com/doc/employee/en/index.html) database schema and data.
I’ve tried these tests with mysqlslap.
```auto
mysqlslap --user="root" --concurrency=100 --iterations={{#iterations}} --delimiter=";" --create-schema="employees" --query="{{a-query-here-}}"
```
**UPDATE**
OS: Slackware 14.0 x64 with 32bit alien multilib support
MySQL: Ver 14.14 Distrib 5.5.27, for Linux (x86\_64) using readline 5.1
RAM: 6GB
CPU: Intel(R) Core™ i5-2410M CPU @ 2.30GHz
Structure( I’ve added only the ones I’ve used ):
```auto
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`),
KEY `emp_no` (`emp_no`),
KEY `dept_no` (`dept_no`),
CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
`name` text NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`),
KEY `emp_no` (`emp_no`),
CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL,
PRIMARY KEY (`emp_no`,`title`,`from_date`),
KEY `emp_no` (`emp_no`),
CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
```
Number of rows in tables:
[TABLE=“width: 500”]
```
[TR]
[TD]
[TD]
9[/TD]
[/TR]
[TR]
[TD]
[TD]
[/TR]
[TR]
[TD]
[TD]
[/TR]
[TR]
[TD]
[TD]
[/TR]
[TR]
[TD]
[TD]
[/TR]
[TR]
[TD]
[TD]
[/TR]
```
[/TABLE]
First test query:
```auto
SELECT employees.* FROM employees
LEFT JOIN dept_emp ON ( dept_emp.emp_no = employees.emp_no )
LEFT JOIN salaries ON ( salaries.emp_no = salaries.emp_no )
WHERE employees.first_name
LIKE '%Jo%'
AND salaries.from_date > '1993-01-21'
AND salaries.to_date < '1998-01-01'
LIMIT 0, 100
```
20 iterations
Average number of seconds to run all queries: 10.109 seconds
Minimum number of seconds to run all queries: 9.946 seconds
Maximum number of seconds to run all queries: 10.542 seconds
Number of clients running queries: 100
Average number of queries per client: 1
100 iterations
Then I’ve altered the table salaries and added a name TEXT column and populated it with its employee related first\_name column value with this sql query:
```auto
```
Then I"ve repeated the previous 2 tests in which I’ve modified the LIKE part. In the first test it was referencing the employee.first\_name. I’ve replaced it with the newly created and populated salaries.name, like this:
```auto
SELECT `employees`.* FROM `employees`
LEFT JOIN `dept_emp` ON ( `dept_emp`.`emp_no` = `employees`.`emp_no` )
LEFT JOIN `salaries` ON ( `salaries`.`emp_no` = `salaries`.`emp_no` )
WHERE[B] `salaries`.`name` LIKE "%Jo%"[/B]
AND `salaries`.`from_date` > '1993-01-21'
AND `salaries`.`to_date` < '1998-01-01'
LIMIT 0, 100
```
And here are the results:
20 Iterations
Average number of seconds to run all queries: 1.505 seconds
Minimum number of seconds to run all queries: 1.452 seconds
Maximum number of seconds to run all queries: 1.557 seconds
Number of clients running queries: 100
Average number of queries per client: 1
100 Iterations
The number of iterations obviously doesn’t have a significant impact. But after moving the first\_name column to to salaries tables, the overall number of seconds is drastically reduced.
Why is the execution time difference so large between the two queries?
---
### Author:  [@gmouse](https://forums.percona.com/u/gmouse)
#### Post date: [April 16, 2013, 8:03am UTC](https://forums.percona.com/t/mysql-performance-test-results-using-employee-database/2632/2 "2013-04-16T08:03:12Z")
The queries are different, you are now running an inner join (not a left join) on the salaries table. That means that the execution order may be different and that an index on a dates field in the salaries table may now be used.
btw. SHOW CREATE TABLE is more readable for showing the table structure (especially for indices).
---
### Author:  [@keeperhood](https://forums.percona.com/u/keeperhood)
#### Post date: [April 16, 2013, 9:21am UTC](https://forums.percona.com/t/mysql-performance-test-results-using-employee-database/2632/3 "2013-04-16T09:21:28Z")
> [@gmouse;9749](#):
>
> The queries are different, you are now running an inner join (not a left join) on the salaries table. That means that the execution order may be different and that an index on a dates field in the salaries table may now be used
.
How am i now running an inner join when the only thing that’s changed is the LIKE parameter?
> [@](#):
>
> btw. SHOW CREATE TABLE is more readable for showing the table structure (especially for indices).
Thanks I’ve updated the structure.
---
### Author:  [@gmouse](https://forums.percona.com/u/gmouse)
#### Post date: [April 16, 2013, 9:48am UTC](https://forums.percona.com/t/mysql-performance-test-results-using-employee-database/2632/4 "2013-04-16T09:48:40Z")
It becomes an inner join since `salaries`.`name` LIKE “%Jo%” does not match NULL values (so, the LEFT JOIN does not make sense).
---
### Author:  [@keeperhood](https://forums.percona.com/u/keeperhood)
#### Post date: [April 16, 2013, 1:31pm UTC](https://forums.percona.com/t/mysql-performance-test-results-using-employee-database/2632/5 "2013-04-16T13:31:57Z")
I guess I’ll have to read more about mysql to fully understand that. The thing that bothers me is those Would that be a reasonable calculation result average with 100 users concurrently quering searches on 1 server?
---
### Author:  [@gmouse](https://forums.percona.com/u/gmouse)
#### Post date: [April 16, 2013, 4:52pm UTC](https://forums.percona.com/t/mysql-performance-test-results-using-employee-database/2632/6 "2013-04-16T16:52:46Z")
You definitely want queries to run faster, since users want instant results if they browse your website.