CODEWITHSUNDEEP
mysqlperformancejoin
Dimas
Dimas

Reputation: 341

MySQL performance issue on a simple join with big tables

I'm migrating a PHP application from MSSQL Server to MySQL and I'm stuck with a simple query that with MSSQL Server lasts 0,5 seconds and with MySQL 50 seconds. Any ideas? Indexes? Server configuration? The MySQL server hardware is equal or better than the MSSQL server.

Table structure

Both are InnoDB:

CREATE TABLE `tb1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nec` INT(11),
    `start_date` DATETIME(6) NOT NULL,
    `end_date` DATETIME(6) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `IX_nec` (`nec`)
);

CREATE TABLE `tb2` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nec` INT(11) NOT NULL,
    `start_date` DATETIME(6) NOT NULL,
    `end_date` DATETIME(6) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `IX_nec` (`nec`)
);

The tables are big. Tb1 has around 300.000 rows and tb2 around 400.000.

Select

SELECT count(1)
FROM tb2
LEFT JOIN tb1 ON tb1.nec = tb2.nec

The result is around 180.000.000.

This is only a sample, the final goal is a bigger query with other date filters/intersections and more.

Explain plan

Explain Plan Query on MySQL

Execution plan from SQL Server: Execution plan from SQL Server Query on SQL Server

Configuration, hardware, ...

@@innodb_buffer_pool_size: 2147483648
SELECT version(): 5.7.17-0ubuntu0.16.04.1
Profiling of the query (CSV file)
Procedure Analyse() (XLS File)
my.cnf
System:
 VMware Virtual Platform
 Intel(R) Xeon(R) CPU E5530 @ 2.40GHz
 4GiB DIMM DRAM EDO
 Ubuntu 16.04.2 LTS (Linux gt 4.4.0-66-generic x86_64)

Upvotes: 1

Views: 316

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

This is not the Answer

I have create 2 sample table with random data (i know its not the same) and 500000 rows each and test your query. It will take 1,1 sec. So i will nearly sure that is a configuration or Hardware Problem. So i will use some more Information. Can you me post the Output of the querys please

1) SELECT VERSION();

2) The output of this

SET PROFILING=ON;

SELECT count(1)
FROM tb2
LEFT JOIN tb1 USING(nec);

SHOW PROFILE ALL;
SET PROFILING=OFF;

3) your my.cnf

4) Some infos about the Hardware and operating system

and you can check also this query. it takes only 500 ms on my Server

SELECT sum(IF(s IS NULL,1,s)) AS cnt
FROM tb2
LEFT JOIN 
  (SELECT DISTINCT nec, SUM(1) AS s FROM tb1 GROUP BY nec) tmp USING (nec) ;

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415790

There's a lot that could be going on here, but given you're in the process of moving from one DB type to another, it's likely a new server. With that in mind, there could just be some basic server tuning that still needs to happen. For example, MySql's buffer pool must be set manually, or it won't use much of the memory in the server. Has this been done?

Upvotes: 0

Related Questions