CODEWITHSUNDEEP
mysqlperformanceleft-join
JoeLoco
JoeLoco

Reputation: 2214

MySQL Join Slow Query

I have 2 table t1 -> t2 (common one to many relationship) with 140.000 records on table t2 reffering 50.000 records on t1, foreing key some times is null (no parent).

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `name_idx` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=125666 DEFAULT CHARSET=utf8

CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`t1_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `t1_id_idx` (`t1_id`)
CONSTRAINT `t1_fk` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=125666 DEFAULT CHARSET=utf8

This query running on 15 seconds:

SELECT * FROM t2
LEFT JOIN t1 ON t2.t1_id = t1.id
ORDER BY t1.name ASC
LIMIT 10;

This query running on 0.5 seconds:

SELECT * FROM t2
LEFT JOIN t1 ON t2.t1_id = t1.id
WHERE t1.name <> 'any not found value'
ORDER BY t1.name ASC
LIMIT 10;

Can any body explain to me why this occurs?

Obs: Edited.

News:

This query running on 0.06 seconds: (WHAT'S CHANGE? inner join!!)

SELECT * FROM t2
INNER JOIN t1 ON t2.t1_id = t1.id
ORDER BY t1.name ASC
LIMIT 10;

but above query does not is a solution for my, in my case t2.t1_id can be null some times.

Any Idea??

News:

Running explain with left and inner join:

Mysql show: Using temporary; Using filesort; Rows: 140.000

With Inner Join:

Mysql show: Using Where; Rows: 8

Upvotes: 1

Views: 2250

Answers (2)

JoeLoco
JoeLoco

Reputation: 2214

Solved!

The Problem is on order by, when using order by mysql create a temporaty file (Explain...Using Temporary), this temporary file is too big causing the lag.

Tips:

  • Avoid Using Tempoaray
  • When Using temporary don't load much data.

Upvotes: 2

Mike
Mike

Reputation: 26

I suspect you already have an index on t1.name but it is descending not ascending. That explains why the second query is so much faster.

The other explanation is the first query was not cached but the second query found data in the cache and ran faster.

Upvotes: 0

Related Questions