Philip
May 21, 2021
144 views
0 votes
2 Answers

I built a website with laravel 8.
The server is a 6 cores CPU / 6 GB Ram VPS. Server is Linux CentOS with nginx and mysql 8.

At peak period, there is about 500 visits online simultaneously. The CPU become 100% during the peak period and >80% in rest of the time.

I checked the usage and found most resource is used by mysql. Then I located some slow queries and I think this many-to-many relationship query is one of the main cause.

There is a Video model and Genre model with many-to-many relationship setup. In the video table, there is about 800,000 rows. There is 700+ genres in genre table and there is 237,4344 relationships in genre_video table. videos.id and genres.id are primary index of videos and genres table respectively. foreign keys are set up in genre_video

Video model

class Video extends Model
{
   use HasFactory;
   public function genres()
    {
        return $this->belongsToMany(Genre::class);
    }

}

Genre Model

class Genre extends Model
{
    use HasFactory;
    public $timestamps = false;
    public function videos()
    {
        return $this->belongsToMany(Video::class);
    }
}

Tables

videos
id           video_info1           video_info2          type_code
1            somethining           somethining          1
2            somethining           somethining          1
3            somethining           somethining          1

genres
id           genre_name
1            G1
2            G2
3            G3
4            G4
5            G5


genre_video
genre_id         video_id
1                1
1                3
1                5
2                1
2                3


previews (one-to-one with video)
id          image
1           aaa.jpg
2           bbb.jpg
3           ccc.jpg


titles (one-to-one with video)
id          title
1           aaa
2           bbb
3           ccc

Filter function

I have a genre list on my website. When visitor click on the genre, it changes the url.

For example:
Gerne list: G1 G2 G3 G4 G5

when visitor click on G1, the url become /?c=1

then visitor click on G3, the url become /?c=1,3

then visitor click on G5, the url become /?c=1,3,5

The function will get all selected genre id as array $cArr. Then I use whereHas to loop through the array to find all videos that match genre 1,3,5. As the visitor add more genre in the filter, they could find exactly what they want. i.e. video with id = 1 in the example. But this query took about 20-50s.

if($request->c){
                $c = $request->c;
                $cArr = explode(',',$c);
                $data = Video::where('type_code',$type_code)
                            ->whereHas('genres',function ($query) use($cArr) {
                                $query->whereIn('genres.genre_id', $cArr);
                                }, '=', count($cArr))
                            ->join('previews','previews.code','=','videos.code')
                            ->join('titles','titles.code','=','videos.code')
                            ->orderBy('publish_date', 'DESC')
                            ->limit(400)->get();
}

$type_code will only equal to 0,1,2,3
previews and titles are one-to-one relationship with videos

My question is:

Is there a way to make this query better but maintain the filter function?
I have check on the internet people say we should use index engine like sphinex. I don’t know if it is compatible with my settings linux + centOS 7 + nginx + mysql 8 + laravel 8. Any advise on using an index engines?

Update

Thank you for taking time to read my question. Here are some example of the actual generated queries. The time is already at the best speed as it has the lowest traffic at the time of the day.

The first one is when the visitor clicked on G2 and G13. He will see 400 videos that with genre G2 and G13.

select * from `videos` 
inner join `previews` on `previews`.`code` = `videos`.`code` 
inner join `titles` on `titles`.`code` = `videos`.`code` 
where `type_code` = 0 and (
    select count(*) 
    from `genres` 
    inner join `genre_video` on `genres`.`id` = `genre_video`.`genre_id` 
    where `videos`.`id` = `genre_video`.`video_id` 
    and `genres`.`genre_id` in ('2', '13')
) = 2 order by `publish_date` desc limit 400

Query took 13.58s

The second one is when the visitor clicked on G2, G13 and G18. He will see even precisely filtered 400 visdeos with all these genres

select * from `videos` 
inner join `previews` on `previews`.`code` = `videos`.`code` 
inner join `titles` on `titles`.`code` = `videos`.`code` 
where `type_code` = 0 and (
    select count(*) from `genres` 
    inner join `genre_video` on `genres`.`id` = `genre_video`.`genre_id` 
    where `videos`.`id` = `genre_video`.`video_id` 
    and `genres`.`genre_id` in ('2', '13', '18')
) = 3 order by `publish_date` desc limit 400

Query took 14.04s

Update 2

I added the columns ,index and relationship screenshots. I am sorry I cannot provide the laravel migration file as I created these table in phpmyadmin before I learn migration. But seems all required relationship is add according to the many-to-many documentation.

actors

videos

actor_vidio

previews

titles

Sorry again for making the question messy.

Update 3

EXPLAIN select * from `videos` 
inner join `previews` on `previews`.`code` = `videos`.`code` 
inner join `titles` on `titles`.`code` = `videos`.`code` 
where `type_code` = 0 and (
    select count(*) from `genres` 
    inner join `genre_video` on `genres`.`id` = `genre_video`.`genre_id` 
    where `videos`.`id` = `genre_video`.`video_id` 
    and `genres`.`genre_id` in ('2', '13', '18')
) = 3 order by `publish_date` desc limit 400

EXPLAIN select * from `videos` 
inner join `previews` on `previews`.`code` = `videos`.`code` 
inner join `titles` on `titles`.`code` = `videos`.`code` 
where `type_code` = 0 and (
    select count(*) from `genres` 
    inner join `genre_video` on `genres`.`id` = `genre_video`.`genre_id` 
    where `videos`.`id` = `genre_video`.`video_id` 
    and `genres`.`genre_id` in ('2', '13', '18')
) = 3 order by `publish_date` desc limit 400

Update 4

I added SHOW CREATE TABLE

actors

actors
CREATE TABLE `actors` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `actor_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL,
 `actor_type` int unsigned NOT NULL DEFAULT '0',
 `actor_img` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `actor_sex` int DEFAULT '2',
 `actor_cn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `actor_tw` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `actor_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `actor_ja` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `actor_ko` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `actor_id` (`actor_id`),
 KEY `actor_sex` (`actor_sex`)
) ENGINE=InnoDB AUTO_INCREMENT=89588 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

videos

CREATE TABLE `videos` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `type_code` int NOT NULL,
 `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `publish_date` date NOT NULL,
 `duration` int NOT NULL,
 `download` int NOT NULL,
 `sub` int NOT NULL,
 `online` int NOT NULL DEFAULT '0',
 `leak` int NOT NULL DEFAULT '0',
 `javdb_url_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 `is_single_actor` int NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `code` (`code`),
 KEY `type_code` (`type_code`),
 FULLTEXT KEY `code_fulltext` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=458527 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

actor_video

CREATE TABLE `actor_video` (
 `video_id` int unsigned NOT NULL,
 `actor_id` int unsigned NOT NULL,
 PRIMARY KEY (`video_id`,`actor_id`),
 KEY `actor_video_actor_id_foreign` (`actor_id`) USING BTREE,
 KEY `actor_video_video_id_foreign` (`video_id`) USING BTREE,
 KEY `actor_id` (`actor_id`),
 KEY `video_id` (`video_id`),
 CONSTRAINT `actress_video_actress_id_foreign` FOREIGN KEY (`actor_id`) REFERENCES `actors` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
 CONSTRAINT `actress_video_video_id_foreign` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

previews

CREATE TABLE `previews` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `video_preview` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `image_pl` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `image_ps` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `image_preview_s` varchar(3000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `image_preview` varchar(3000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=458096 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

titles

CREATE TABLE `titles` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `title_cn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `title_tw` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `title_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `title_ja` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `title_ko` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `code` (`code`),
 FULLTEXT KEY `title_index` (`title_ja`,`title_en`) /*!50100 WITH PARSER `ngram` */ ,
 CONSTRAINT `title_video_fk` FOREIGN KEY (`code`) REFERENCES `videos` (`code`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=458101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

genres

CREATE TABLE `genres` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `genre_id` int NOT NULL,
 `genre_type` int NOT NULL DEFAULT '0',
 `genre_cn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `genre_tw` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `genre_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `genre_ja` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `genre_ko` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `type_code` int NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `genre_id` (`genre_id`),
 FULLTEXT KEY `genre_cn` (`genre_cn`,`genre_tw`,`genre_en`,`genre_ja`)
) ENGINE=InnoDB AUTO_INCREMENT=1536 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

genre_video

CREATE TABLE `genre_video` (
 `genre_id` int unsigned NOT NULL,
 `video_id` int unsigned NOT NULL,
 KEY `genre_video_genre_id_foreign` (`genre_id`),
 KEY `genre_video_video_id_foreign` (`video_id`),
 KEY `genre_id` (`genre_id`),
 CONSTRAINT `genre_video_genre_id_foreign` FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`) ON DELETE CASCADE,
 CONSTRAINT `genre_video_video_id_foreign` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Answers

- RickJames
- May 21, 2021 at 7:17 am
- 0 votes
0
Many:to:many tables tend to be poorly indexed, leading to lots of extra CPU. This shows the optimal schema (no auto_inc) and indexing (2 composite indexes):

http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Login or Signup to reply.

First, your common key for your JOINs is code.

It’s defined like this in titles

code varchar(50)

and like this in videos and previews

code varchar(255)

That’s not good for performance in ON conditions. Define all three code columns precisely the same.

Next issue: Let’s bid adieu to the dependent subquery.

We can write it

              select genre_video.video_id
                     from genres
                     join genre_video ON genres.id = genre_video.id
                     join videos on genre_video.video_id = videos.id
                    where genres.genre_id IN (2, 13, 18)
                      and videos.type_code = 0
                 group by genre_video.video_id
                   having COUNT(*) = 3

to get the video_id values matching the three genres.

Then we JOIN the subquery into another yet subquery. This does the heavy lifting of order by ... limit 400.

      select code
        from (
                   select genre_video.video_id
                     from genres
                     join genre_video ON genres.id = genre_video.id
                     join videos on genre_video.video_id = videos.id
                    where genres.genre_id IN (2, 13, 18)
                      and videos.type_code = 0
                 group by genre_video.video_id
                   having COUNT(*) = 3
             ) matches
        join videos ON matches.video_id = videos.id
       order by publish_date desc
       limit 400

Only then do we join to the various tables and do the select *

select *
  from (
          select code
            from (
                  select genre_video.video_id
                         from genres
                         join genre_video ON genres.id = genre_video.id
                         join videos on genre_video.video_id = videos.id
                        where genres.genre_id IN (2, 13, 18)
                          and videos.type_code = 0
                     group by genre_video.video_id
                       having COUNT(*) = 3
                 ) matches
            join videos ON matches.video_id = videos.id
           order by publish_date desc
           limit 400
       ) chosen
  join videos on chosen.code = videos.code
  join titles on chosen.code = titles.code
 order by videos.publish_date;

This should help a lot.

Sorry to say, I don’t know how to code this in Laravel.

You didn’t show us genres, But it should have these two indexes for this kind of thing to work efficiently. It doesn’t need indexes on either individual column.

PRIMARY KEY (video_id, genre_id)
INDEX (genre_id, video_id)

(By the way, this advice about indexing applies to actor_video too.)

Tricky problems like this are the sign of real application. You should monitor performance as your app grows. You may need other indexes, or need to refactor other queries.

Please signup or login to give your own answer.

Click here to cancel reply.

How to improve query speed on Laravel many to many relationship in a filter system? – CentOS

Update

Update 2

Update 3

Update 4

Answers