Optimizing a query on Mysql 5.5.24

Question

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.

• Colin 't Hart: Do you really mean to do a Cartesian product of the two tables? Your result will have 6 million rows, so 20 seconds seems very reasonable!

Answer 1

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

• Chris H: Thanks a lot Rick. I had to do a left join, but now the query runs in 4 seconds. Thanks again.
• Rick James: Glad you are happy. I was expecting much more than 5x improvement. (Although, I did not really expect 1000x.)
• Chris H: Thanks Rick, I used Query cache thinking it would speed up things and found that when searching for optimizing a query on MySQL database. I will need to search and read some blogs/books on tuning databases. Would you recommend any? Thanks for helping the community.
• Rick James: There are a number of good books out there, but I am biased toward my blogs which cover a number of common issues. The blogs on Memory allocation (which includes Query cache) and Cookbook for Creating Indexes are perhaps most relevant to this thread.