I was previously using SQLite for a personal project and due to a constraint of having it available online, I decided to make the switch to MySQL. I converted my database to the MySQL equivalent but I just noticed that performance is VERY poor. This is a 70 mb database with around 600k records total. The query I am running is an INNER JOIN that executes in less than 500 ms using SQLite but the same query using MySQL takes 15 minutes.
SELECT has.tag_id, has.image_id
FROM has
INNER JOIN image ON image.image_id = has.image_id
INNER JOIN person ON person.person_id = image.person_id
WHERE person.name="Random Person"
has
table has 80k recordsimage
table has 290k recordsperson
table has 500 records
Here is the structure of the three tables:
create table media.person
(
person_id int auto_increment
primary key,
name text not null
)
collate = utf8_unicode_ci;
create table media.image
(
id int auto_increment,
image_id int not null,
person_id int not null,
link text not null,
checksum text null,
constraint id
unique (id)
)
collate = utf8_unicode_ci;
alter table media.image
add primary key (id);
create table media.has
(
id int auto_increment
primary key,
tag_id int not null,
image_id int not null
)
collate = utf8_unicode_ci;
Note that I added a primary key to the has
table because I suspected it might have been the source of the problem, but it isn't and SQLite was doing fine without that primary key.
The database uses the InnoDB engine. Here is the output of the mysql --version
command:
mysql Ver 14.14 Distrib 5.7.30
Where could the problem come from? I can understand a small loss of performance because MySQL is heavier than SQLite but certainly not to the point of going from 500 ms to 15 minutes for such a simple query.
EDIT:
I just ran the query again, this time it took 7 minutes. I used slow_query_logs but it doesn't tell me much.
# Time: 2020-07-13T07:18:22.526915Z
# User@Host: media[media] @ localhost [127.0.0.1] Id: 205
# Query_time: 429.434270 Lock_time: 0.000751 Rows_sent: 501 Rows_examined: 223451
use media;
SET timestamp=1594624702;
/* ApplicationName=DataGrip 2020.1.3 */ SELECT has.tag_id, has.image_id
FROM has
INNER JOIN image ON image.image_id = has.image_id
INNER JOIN person ON person.person_id = image.person_id
WHERE person.name="Random Person";