0

log from mysql-slow-log:

Query_time: 11.632750 Lock_time: 1.317329 Rows_sent: 851 Rows_examined: 1649439

select record.name, dept.name, school.name FROM record

inner join record as r2 ON
record.number=r2.number AND record.year=r2.year
AND r2.dept_id=12345 AND r2.type=1 AND r2.year=2015

LEFT JOIN school ON record.school_id=school.id
LEFT JOIN dept ON record.dept_id=dept.id

There are about 0.8 million rows in table record but how come the query examine more than 1 million rows?

I've set the index for table dept and table school, but I'm not sure about how to create index for the inner join part. I read a previous question(Rows_sent: 12 Rows_examined: 549024 - how to optimize mySQL query?) but still can't get the solution to my extremely slow query.

Edited (why inner join):

I would like to select all rows from same persons having dept_id=12345. The result would be the yellow and orange rows as in the example picture. That's why I use inner join.

example of table record

Community
  • 1
  • 1
benck
  • 2,034
  • 1
  • 22
  • 31
  • Why are you joining record to itself? In this case that doesn't seem to serve any purpose does it? – Hanky Panky Apr 17 '15 at 06:19
  • 3
    Run the following and share the result into the question.. `explain select ...` – Abhik Chakraborty Apr 17 '15 at 06:21
  • ensure there are indexes on `record.school_id` and `record.dept_id`, and get rid of the self join on the records table. it looks pointless. – pala_ Apr 17 '15 at 06:37
  • I add some words to explain why the inner join is needed. – benck Apr 17 '15 at 06:55
  • What's wrong with `WHERE dept_id=12345` and then process the result further to get the other departments these people belong to? – Bart Friederichs Apr 17 '15 at 06:59
  • You mean the first query is to use dept_id and get all the numbers. The second query use those number and write `number in (001,002,004)` to get those rows? I thought writing one query and combining those two actions would be faster. – benck Apr 17 '15 at 07:03

1 Answers1

0

Why 1.6M, not 0.8M? It seems to

  1. read all of one table (0.8M),
  2. for each of those rows, reach into the other table (another 0.8M).

For this tally, it is irrelevant that both tables are the same table ("self join").

How to optimize? It would be easier to answer if you had provided SHOW CREATE TABLE record, but here's a guess.

Add two indexes:

INDEX(dept_id, year, type) -- the fields can be in any order
INDEX(number, year) -- the fields can be in either order

The first one gets you into r2 very efficiently. The second one makes the JOIN very efficient.

There is a problem with select record.name, dept.name, school.name FROM record -- you have not JOINed to the tables dept or school.

Building an INDEX from a SELECT

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thank you. The problem was indeed about `index`. The indexes you suggested were already built and after my investigation, the problem is in the table `dept`. I build two indexes in table `dept`, but however, mysql automatically select the wrong index so it traverse through whole `dept` table for each left join. Using `FORCE INDEX` in the LEFT JOIN statement works! – benck Apr 18 '15 at 03:24