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.

Best Answer

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

Related Question