MySQL performance test results using employee database

MySQL & MariaDB Other MySQL® Questions
1

I’ve used the http://MySQL :: Employees Sample Database database schema and data.

I’ve tried these tests with mysqlslap. 

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 ):


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:


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:

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:


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?

2

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).

3

.
How am i now running an inner join when the only thing that’s changed is the LIKE parameter?

Thanks I’ve updated the structure.

4

It becomes an inner join since salaries.name LIKE “%Jo%” does not match NULL values (so, the LEFT JOIN does not make sense).

5

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?

6

You definitely want queries to run faster, since users want instant results if they browse your website.