0

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 records
  • image table has 290k records
  • person 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";
9
  • @Lennart thank you, that was indeed the problem. Adding a primary key image_id, tag_id reduced the query time from 15 minutes to 600 ms.
    – Keagel
    Commented Jul 13, 2020 at 10:12
  • checkout explain. It will help you realize how the DBMS evaluates your questions. Commented Jul 13, 2020 at 10:18
  • I just tried it and unfortunately the most useful part for me - EXPLAIN ANALYZE - isn't available as I'm using MySQL 5.7. The query still takes around 600 ms compared to the 280-300 ms it takes using SQLite but EXPLAIN shows me that it's already using indexes and primary keys so I suppose it can't do much more.
    – Keagel
    Commented Jul 13, 2020 at 11:46
  • You should also add an index on Image (image_id, person_id) Commented Jul 13, 2020 at 11:55
  • I already have two separate indexes for image_id and person_id so unfortunately it still takes 600 ms.
    – Keagel
    Commented Jul 13, 2020 at 11:58

1 Answer 1

3

You need to add indexes:

  1. image (person_id, image_id)
  2. has (image_id, tag_id) - you can remove id column from this table and make this index a primary key
  3. It is not the best idea to use "text" data type for person.name column. It would be better to use varchar or nvarchar. For example, varchar(100) should be enough. Then create index on this column too: person (name).
1
  • Thanks for the tips. I added a primary key between image_id and tag_id and it already improved by a lot but I'll make sure to add indexes too.
    – Keagel
    Commented Jul 13, 2020 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.