How to Keep Laravel SQl Query Time Under 5 seconds

Question

My php application uses laravel 4.1.31. For database queries, it uses knockout js library through ajax. When the database size grows larger, the ajax loading becomes a problem because it takes too long and sometimes stops halfway. I'm not familiar with the libraries so I can't do any application level optimization.

I hope that I can still do optimization on the database query and keep each query under 5 seconds.

Here are some of information I can share:

RAM: 2GB 1core Debian 9. Single Innodb database.

The Slow query log:

# Time: 190611  7:49:08
# User@Host: user[user] @ localhost []
# Thread_id: 690728  Schema: user  QC_hit: No
# Query_time: 9.343611  Lock_time: 0.000030  Rows_sent: 100  Rows_examined: 440481
# Rows_affected: 0
use user;
SET timestamp=1560239348;
select * from `titles` where `titles`.`type` = 'movie'
       order by `tmdb_rating` desc limit 100 offset 86500;

SHOW CREATE TABLE titles;

CREATE TABLE `titles` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `type` enum('movie','series') COLLATE utf8_unicode_ci DEFAULT NULL,
 `imdb_rating` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `tmdb_rating` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `mc_user_score` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `mc_critic_score` smallint(5) unsigned DEFAULT NULL,
 `mc_num_of_votes` int(10) unsigned DEFAULT NULL,
 `imdb_votes_num` bigint(20) unsigned DEFAULT NULL,
 `release_date` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `year` smallint(5) unsigned DEFAULT NULL,
 `plot` text COLLATE utf8_unicode_ci,
 `genre` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `tagline` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `poster` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `background` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `awards` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `runtime` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `trailer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `budget` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `revenue` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `views` bigint(20) NOT NULL DEFAULT '1',
 `tmdb_popularity` float(50,2) unsigned DEFAULT NULL,
 `imdb_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `tmdb_id` bigint(20) unsigned DEFAULT NULL,
 `season_number` tinyint(3) unsigned DEFAULT NULL,
 `fully_scraped` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `allow_update` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `featured` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `now_playing` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NULL DEFAULT NULL,
 `temp_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `language` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `country` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `original_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `affiliate_link` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `custom_field` text COLLATE utf8_unicode_ci,
 PRIMARY KEY (`id`),
 UNIQUE KEY `titles_imdb_id_unique` (`imdb_id`),
 UNIQUE KEY `titles_tmdb_id_type_unique` (`tmdb_id`,`type`),
 KEY `titles_mc_num_of_votes_index` (`mc_num_of_votes`),
 KEY `titles_created_at_index` (`created_at`),
 KEY `titles_release_date_index` (`release_date`),
 KEY `titles_title_index` (`title`),
 KEY `titles_mc_user_score_index` (`mc_user_score`),
 KEY `titles_tmdb_popularity_index` (`tmdb_popularity`),
 KEY `titles_temp_id_index` (`temp_id`),
 KEY `titles_tmdb_rating_index` (`tmdb_rating`)
) ENGINE=InnoDB AUTO_INCREMENT=18712721 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

EXPLAIN select * from titles where titles.type = 'movie' order by tmdb_rating desc limit 100 offset 86500;

+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
|    1 | SIMPLE      | titles | ALL  | NULL          | NULL | NULL    | NULL | 311859 | Using where; Using filesort |
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.01 sec)

my.cnf Settings:

default-storage-engine = InnoDB
symbolic-links=0
skip-external-locking
max_allowed_packet = 16M
table_open_cache = 5000
query_cache_size = 0
query_cache_type = 0
thread_cache_size = 4
tmp_table_size = 256M
max_heap_table_size = 256M
performance_schema = ON
key_buffer_size = 140k
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256k
myisam_sort_buffer_size = 140k
join_buffer_size = 2M

innodb_file_per_table
innodb_buffer_pool_size = 512M
innodb_log_file_size = 200M
innodb_buffer_pool_instances = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 4
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 32M
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000
sync_binlog = 1

max_connections=100
max_user_connections=100
wait_timeout=10
interactive_timeout=30
long_query_time=5
slow-query-log =1
slow-query-log-file = /var/log/mysql/mysql-slow.log

Memory Usage:

Private  +   Shared  =  RAM used       Program

224.0 KiB +  26.5 KiB = 250.5 KiB       agetty (2)
248.0 KiB +  41.0 KiB = 289.0 KiB       sftp-server
180.0 KiB + 124.0 KiB = 304.0 KiB       anvil
404.0 KiB +  31.5 KiB = 435.5 KiB       vsftpd
392.0 KiB + 104.5 KiB = 496.5 KiB       dovecot
564.0 KiB +  39.5 KiB = 603.5 KiB       memcached
484.0 KiB + 122.0 KiB = 606.0 KiB       log
828.0 KiB +  44.0 KiB = 872.0 KiB       systemd-udevd
968.0 KiB +  42.0 KiB =   1.0 MiB       ntpd
732.0 KiB + 307.0 KiB =   1.0 MiB       systemd-logind
684.0 KiB + 355.0 KiB =   1.0 MiB       sh (8)
340.0 KiB + 754.0 KiB =   1.1 MiB       vesta-nginx (2)
  1.0 MiB +  58.5 KiB =   1.1 MiB       dbus-daemon
880.0 KiB + 250.5 KiB =   1.1 MiB       auth
760.0 KiB + 496.0 KiB =   1.2 MiB       flock (6)
  1.2 MiB + 244.0 KiB =   1.4 MiB       config
  1.7 MiB + 205.5 KiB =   1.9 MiB       systemd-journald
  1.9 MiB +  47.0 KiB =   1.9 MiB       rsyslogd
  2.1 MiB + 103.0 KiB =   2.2 MiB       exim4
  2.5 MiB +  76.0 KiB =   2.6 MiB       bash
  2.5 MiB +   1.3 MiB =   3.8 MiB       cron (9)
  2.2 MiB +   2.1 MiB =   4.3 MiB       sshd (4)
  2.2 MiB +   2.1 MiB =   4.4 MiB       nginx (3)
500.0 KiB +   4.8 MiB =   5.2 MiB       vesta-php (3)
  4.3 MiB +   3.7 MiB =   7.9 MiB       systemd (5)
 15.2 MiB + 109.5 KiB =  15.3 MiB       fail2ban-server
 67.3 MiB +  15.4 MiB =  82.6 MiB       php7.0 (8)
100.9 MiB +  67.2 MiB = 168.1 MiB       apache2 (12)
758.7 MiB + 156.0 KiB = 758.9 MiB       mysqld

TOP Command:

top - 14:10:43 up 7 days, 23:12,  1 user,  load average: 0.79, 0.91, 0.69
Tasks: 145 total,   4 running, 141 sleeping,   0 stopped,   0 zombie
%Cpu(s): 19.1 us,  6.0 sy,  0.0 ni, 71.9 id,  2.7 wa,  0.0 hi,  0.3 si,  0.0 st
KiB Mem :  2052588 total,   501496 free,  1085916 used,   465176 buff/cache
KiB Swap:        0 total,        0 free,        0 used.   735980 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
23688 mysql     20   0 1211616 770288      0 S  7.3 37.5 543:04.35 mysqld
29889 admin     20   0  539584  53996  38624 R  3.7  2.6   0:00.74 apache2
27140 www-data  20   0  537900  64576  50864 S  3.0  3.1   0:10.99 apache2
29901 admin     20   0  539484  53356  38100 S  2.3  2.6   0:00.71 apache2
29972 admin     20   0  538404  46336  32220 S  2.3  2.3   0:00.24 apache2
29297 admin     20   0  539572  64000  48648 S  2.0  3.1   0:02.00 apache2
30084 www-data  20   0  536388  43876  31700 S  1.3  2.1   0:00.24 apache2
30063 admin     20   0  392344  34440  21948 S  1.0  1.7   0:00.54 php
30042 admin     20   0  311432  30956  21628 S  0.7  1.5   0:00.10 php
30347 root      20   0   44888   3616   3016 R  0.7  0.2   0:00.04 top
    7 root      20   0       0      0      0 R  0.3  0.0   8:52.43 rcu_sched
 4834 root      20   0       0      0      0 S  0.3  0.0   0:01.46 kworker/u2:2
23741 memcache  20   0  335680    424      0 S  0.3  0.0   2:41.11 memcached
29002 www-data  20   0  537740  70296  56792 S  0.3  3.4   0:03.03 apache2
29279 www-data  20   0  537504  59164  45840 S  0.3  2.9   0:01.75 apache2
29709 root      20   0       0      0      0 S  0.3  0.0   0:00.07 kworker/0:0
    1 root      20   0  204556   4732   3076 S  0.0  0.2   0:21.00 systemd
    2 root      20   0       0      0      0 S  0.0  0.0   0:00.01 kthreadd
    3 root      20   0       0      0      0 R  0.0  0.0   8:12.40 ksoftirqd/0

Another Slow Query Example:

# Query_time: 21.742335  Lock_time: 0.000042  Rows_sent: 0  Rows_examined: 354027
# Rows_affected: 0
SET timestamp=1560293586;
select * from `titles`
    where `titles`.`type` = 'movie'
      and ( select count(*)
            from `actors`
            inner join `actors_titles` on `actors`.`id` = `actors_titles`.`actor_id`
            where `actors_titles`.`title_id` = `titles`.`id`
              and `name` like 'teren' 
          ) >= 1
    order by `mc_num_of_votes` asc
    limit 100 offset 0;

Reason: 'limit 100 offset 86500' causes almost 90k records fetching. DB must fetch 86600 records and skip 86500 to get 100. — PeliCan, Jun 11 '19 at 10:35
@PeliCan is right not to mention the sorting on 90k records which is needed, most likely MySQL also would need to use a temporary table to sort.. Topicstarter can you provide a `EXPLAIN query` ? — Raymond Nijland, Jun 11 '19 at 10:51
i would be tempted to rewite this more or less as `select * from titles where titles.id > 86500 AND titles.type = 'movie' order by titles.tmdb_rating desc, titles.id desc limit 100;` and track/calculate the needed "offset" (`titles.id > 86500`) in the PHP application. — Raymond Nijland, Jun 11 '19 at 11:04
@Raymond Nijland EXPLAIN: ```+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+ | 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 311859 | Using where; Using filesort |``` — Drunken M, Jun 11 '19 at 11:13
@Raymond Nijland It seems like I can't figure out the query pattern within the application. I rely on the mysql server side optimization. I have added some more information, perhaps you can give me some suggestion. Thanks! — Drunken M, Jun 11 '19 at 14:19
the best way is to handle it in the application so i would suggest into trying a bit harder and spend more time on it, on MySQL it's not possible also not when changing settings.. — Raymond Nijland, Jun 11 '19 at 14:26
Maybe one option in MySQL is to [simulate](http://www.coding-dude.com/wp/databases/creating-mysql-materialized-views/) a Materialized view/indexed view which you prefill with data.. But then you are storing duplicated data and also it brings in complexity to update with the values change.. But instead of using `limit 100 offset 86500;` you should be using a position column within a "group" (`type enum('movie','series')`) like `WHERE position >= 86500 LIMIT 100` — Raymond Nijland, Jun 11 '19 at 14:34
@Raymond Nijland Do you mean by adding a new group key for a position column? Which one? — Drunken M, Jun 12 '19 at 02:15
@Raymond Nijland Do you think the indexes can still be optimized? — Drunken M, Jun 12 '19 at 02:49
If you had a simple index for titles.type then EXPLAIN may show the index being used rather than ALL scan of your entire table. — Wilson Hauck, Jun 12 '19 at 13:08
@DrunkenM Did the additional index improve response time? Appx time before and time after, please. An upvote by the suggestion would be appreciated. — Wilson Hauck, Jun 21 '19 at 00:59

Rick James · Accepted Answer · 2019-06-12T02:54:09.497

2

Ugh: limit 100 offset 86500

Don't do pagination that way. Instead "remember where you left off". This has the drawback of no providing "jump to page 864", but who does that. And who does Next-next-next... 865 times?

Further discussion about this common problem: http://mysql.rjweb.org/doc.php/pagination

And it discusses the "left off" solution in more detail.

Another potential problem: order by tmdb_rating desc -- Perhaps multiple titles have the same "rating"? If so, in what order do you want the titles listed? The simple answer is to specify some unambiguous (but somewhat arbitrary) order: order by tmdb_rating desc, id desc.

Remembering where you left off in a compound order-by is more complex, but possible.

Other notes:

WHERE type=... ORDER BY rating can benefit from the "composite" INDEX(type, rating), in this order.
3 unique keys sounds wrong.
2 TEXT columns being fetched hurts the performance. Don't do SELECT * unless you really need all the columns.
tmp_table_size = 256M and max_heap_table_size = 256M are dangerously high for a tiny 2GB of RAM. Shrink them to only 1% of RAM.
Instead of ( SELECT COUNT(*) FROM...) >=1, do EXISTS ( SELECT 1 FROM ...)

edited Jun 12 '19 at 02:54

answered Jun 11 '19 at 17:38

Rick James

135,179
13
127
222

From the create table info, can it be optimize by adding some join indexes or group keys? – Drunken M Jun 12 '19 at 02:18
I have added another slow query log in the question. Do you think it still can be optimized by adding indexes? – Drunken M Jun 12 '19 at 02:26
@DrunkenM - I added more. – Rick James Jun 12 '19 at 02:54
What do you think with this ```CREATE UNIQUE INDEX titles_composites ON titles (id, tmdb_id, type, mc_num_of_votes);``` Is it better than not unique index? – Drunken M Jun 12 '19 at 12:37
@DrunkenM - For `actors_titles` follow the advice in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table – Rick James Jun 13 '19 at 05:43
@DrunkenM - what table contains `name`?? (It's hard to understand your latest query since `name` is not qualified by a table.) – Rick James Jun 13 '19 at 05:44
its in the table ```actors``` . Btw you suggest ```INDEX(type, rating)```, i ask you whether i can add more columns and as unique ```UNIQUE INDEX(id, tmdb_id, type, mc_num_of_votes)``` . Is it worst or better? – Drunken M Jun 13 '19 at 11:40
Can you help me with laravel? I have difficulty to find out their query codes. PM me admin[@]idigotech.ga – Drunken M Jun 13 '19 at 12:54
You can have multiple columns in a `PRIMARY KEY`, `UNIQUE`, or `INDEX`. But a PK / UNIQUE should have only the minimal number of columns to make "uniqueness". In your case, `(id)` is unique, therefore tacking more on the end is not useful. – Rick James Jun 13 '19 at 16:42
I am not familiar with Laravel. As with most 3rd party interfaces, you are stuck with learning both the interface _and_ MySQL (at least to do more than trivial projects). – Rick James Jun 13 '19 at 16:44
@DrunkenM - and I suggested `INDEX(type, rating)` because that particular combination would help with the particular query. Other indexes may be useful for other queries. – Rick James Jun 13 '19 at 16:45

How to Keep Laravel SQl Query Time Under 5 seconds

1 Answers1