CODEWITHSUNDEEP
mysqlperformancebigdata
John Ferguson
John Ferguson

Reputation: 3

Query Slowness Between Two Tables

If there is a result matching the value of "parent_id" in my "table1" and "table2" tables, I want to get the number of rows in the "table1" table.

But the SQL query takes too long.

There are 100 thousand rows in table1.

There are 40 thousand rows in table2.

A table data file for you to try See: https://pastebin.pl/view/raw/ddf8c467

Table Structure

CREATE TABLE table1 (id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
parent_id INT(11) UNSIGNED NOT NULL ,
tes1 INT(1) NOT NULL , PRIMARY KEY (id)) ENGINE = MyISAM;

CREATE TABLE table2 (id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
parent_id INT(11) UNSIGNED NOT NULL ,
tes2 INT(1) NOT NULL , PRIMARY KEY (id)) ENGINE = MyISAM;

SQL Query I use

SELECT     COUNT(A.id) AS total
FROM       table1 A
LEFT JOIN  table2 B ON A.parent_id = B.parent_id
WHERE      B.id IS NOT NULL

Upvotes: 0

Views: 25

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

create a index on parent_id on table B and use INNODB if possible.

you can also use inner join

SELECT     COUNT(A.id) AS total
FROM       table1 A
INNER JOIN  table2 B ON A.parent_id = B.parent_id;

Upvotes: 1

Related Questions