# Mysql – Optimizing a query on Mysql 5.5.24

MySQLperformancequery-performance

I am trying to optimize the query

SELECT
CONCAT(lastname, ', ', title, ' ', firstname) AS fullName,
e.lastname,
e.firstname,
e.email,
e.userType,
e.userID
FROM
employee e,
employee_categories ec
WHERE
1 = 1


which is taking 20 seconds to run. The employee table has 6000 records(number of employees) and employee_categories table has 1000 records. Both tables are indexed and index is the primary key in both tables. The index type is unique and index method is BTREE.

I tried

SELECT
SQL CACHE CONCAT(lastname, ', ', title, ' ', firstname) AS fullName,
e.lastname,
e.firstname,
e.email,
e.userType,
e.userID
FROM
employee e,
employee_categories ec
WHERE
1 = 1


I set query_cache_size to 32MB, ensured have_query_cache is set to Yes, query_cache_type is set to 1. I am using MySql 5.5.24 with InnoDB as storage engine

What else can I try?

Any suggestions would be appreciated.

Without some way of tying the two tables together, you are asking for 6 million rows (6000*1000).

Instead of FROM employee e, employee_categories ec, you need something like

FROM employee e
JOIN employee_categories ec  ON e.category = ec.category


AND... be sure category (or whatever it is called) is indexed (perhaps PRIMARY KEY) in one of the tables.

Those changes should speed it up by a factor of 1000 or so.

The Query cache is irrelevant (and generally should be turned off).