MySQL performance test results using employee database

I’ve used the http://dev.mysql.com/doc/employee/en/index.html 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® 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?

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

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

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

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?

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