AVideo
AVideo copied to clipboard
WWBN
Trying to see what can be done about slow queries
I have installed a setup with over 100k embedded videos and the pages are slow. I tried to understand where the issue is and I see that the sql queries are taking very long time. It's possible to optimize these.
I am bringing here the EXPLAIN ANALYZE result of an example query:
USE AVideo;
EXPLAIN ANALYZE
SELECT u.*, v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified FROM videos as v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 2=2 AND u.status = 'a' AND v.status IN ('a','k','f') ORDER BY likes DESC LIMIT 36, 12;
-> Limit/Offset: 12/36 row(s) (actual time=19778.951..19779.125 rows=12 loops=1)
-> Sort row IDs: v.likes DESC, limit input to 48 row(s) per chunk (actual time=19778.479..19779.106 rows=48 loops=1)
-> Table scan on <temporary> (cost=0.01..4837.35 rows=386788) (actual time=0.031..1718.577 rows=990067 loops=1)
-> Temporary table (cost=310502.09..315339.43 rows=386788) (actual time=17940.876..19702.906 rows=990067 loops=1)
-> Nested loop left join (cost=271823.28 rows=386788) (actual time=2548.884..5093.473 rows=990067 loops=1)
-> Inner hash join (u.id = v.users_id) (cost=136447.47 rows=386788) (actual time=2548.865..4855.112 rows=990067 loops=1)
-> Filter: (u.`status` = 'a') (cost=0.01 rows=1) (actual time=0.037..0.042 rows=1 loops=1)
-> Table scan on u (cost=0.01 rows=1) (actual time=0.032..0.037 rows=1 loops=1)
-> Hash
-> Filter: (v.`status` in ('a','k','f')) (cost=93407.27 rows=386788) (actual time=0.025..1686.450 rows=990067 loops=1)
-> Table scan on v (cost=93407.27 rows=773573) (actual time=0.024..1513.710 rows=990067 loops=1)
-> Single-row index lookup on c using PRIMARY (id=v.categories_id) (cost=0.25 rows=1) (actual time=0.000..0.000 rows=1 loops=990067)
You can see that the main issue is the temporary table scan which happens because of the ORDER BY likes. I don't know what is creating this query but it's a show stopper for big systems. It happens both with MySQL and MariaDB. I do know that in PostgreSQL there are much advanced SQL and indexing features that can allow support for bigger systems.
If anyone can think of a way to make the system be able to support 100k+ videos it would be pretty nice.
Thanks, Eliezer
I have tried to optimize the query a bit just to clear out the doubts about the right way the query should run and the next query runs so fast that the above automatically composed query seems to me a bit non-optimized. The next runs really fast
SELECT * FROM (SELECT u.id as uid, u.status as ustatus , v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified
FROM videos as v
LEFT JOIN categories c ON categories_id = c.id
LEFT JOIN users u ON v.users_id = u.id
WHERE 2=2 AND v.status IN ('a','k','f')
ORDER BY likes DESC LIMIT 36, 12) a WHERE a.ustatus IN ('a') ;
output of the analyze in json format
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.486947814,
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"r_loops": 1,
"rows": 48,
"r_rows": 12,
"r_table_time_ms": 0.040543887,
"r_other_time_ms": 0.030390547,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "a.ustatus = 'a'",
"materialized": {
"query_block": {
"select_id": 2,
"r_loops": 1,
"r_total_time_ms": 0.299965536,
"table": {
"table_name": "v",
"access_type": "index",
"possible_keys": ["video_status_idx", "videos_status_index"],
"key": "videos_likes_index",
"key_length": "5",
"used_key_parts": ["likes"],
"r_loops": 1,
"rows": 782598,
"r_rows": 48,
"r_table_time_ms": 0.19286135,
"r_other_time_ms": 0.060234002,
"filtered": 50.00025558,
"r_filtered": 100,
"attached_condition": "v.`status` in ('a','k','f')"
},
"table": {
"table_name": "c",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"ref": ["AVideo.v.categories_id"],
"r_loops": 48,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.002613886,
"r_other_time_ms": 9.205046e-4,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "u",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"ref": ["AVideo.v.users_id"],
"r_loops": 48,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.00171596,
"r_other_time_ms": 4.515683e-4,
"filtered": 100,
"r_filtered": 100
}
}
}
}
}
}
Compared to the original query:
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 9643.807074,
"filesort": {
"sort_key": "v.likes desc",
"r_loops": 1,
"r_total_time_ms": 756.223544,
"r_limit": 48,
"r_used_priority_queue": true,
"r_output_rows": 49,
"r_sort_mode": "sort_key,rowid",
"temporary_table": {
"table": {
"table_name": "u",
"access_type": "ref",
"possible_keys": ["PRIMARY", "users_status_IDX"],
"key": "users_status_IDX",
"key_length": "1",
"used_key_parts": ["status"],
"ref": ["const"],
"r_loops": 1,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.034852389,
"r_other_time_ms": 0.020715696,
"filtered": 100,
"r_filtered": 100,
"index_condition": "u.`status` = 'a'"
},
"table": {
"table_name": "v",
"access_type": "ref",
"possible_keys": [
"fk_videos_users_idx",
"video_status_idx",
"videos_status_index"
],
"key": "fk_videos_users_idx",
"key_length": "4",
"used_key_parts": ["users_id"],
"ref": ["AVideo.u.id"],
"r_loops": 1,
"rows": 391299,
"r_rows": 990067,
"r_table_time_ms": 2691.791742,
"r_other_time_ms": 6129.887127,
"filtered": 50.00025558,
"r_filtered": 100,
"attached_condition": "v.`status` in ('a','k','f')"
},
"table": {
"table_name": "c",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"ref": ["AVideo.v.categories_id"],
"r_loops": 990067,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.00546745,
"r_other_time_ms": 0.569425897,
"filtered": 100,
"r_filtered": 100
}
}
}
}
}
What slows down the whole process is the creation of a temporary table that isn't really optimized. What do you thing @DanielnetoDotCom
Hi
thanks for that effort, Let's compare both queries ...
Original
SELECT u.*, v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified
FROM videos as v
LEFT JOIN categories c ON categories_id = c.id
LEFT JOIN users u ON v.users_id = u.id
WHERE 2=2 AND u.status = 'a' AND v.status IN ('a','k','f')
ORDER BY likes DESC LIMIT 36, 12;
With this query, we will select user, videos, and category info all at once
Optmized
SELECT * FROM (SELECT u.id as uid, u.status as ustatus , v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified
FROM videos as v
LEFT JOIN categories c ON categories_id = c.id
LEFT JOIN users u ON v.users_id = u.id
WHERE 2=2 AND v.status IN ('a','k','f')
ORDER BY likes DESC LIMIT 36, 12) a WHERE a.ustatus IN ('a') ;
This query seems good but may return fewer results, even can return an empty result when there are results For example, if you return the subquery videos from inactive users
Checks
Can you please check if your tables have indexes for those columns?
Videos Table
- v.categories_id
- v.users_id
- v.status
- v.likes
- v.dislikes
User Table
- u.status
@DanielnetoDotCom You are the designer of the DB and I believe you should know the answers already. The result would be the same if you would run:
SELECT * FROM (SELECT u.id as uid, u.status as ustatus , v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified
FROM videos as v
LEFT JOIN categories c ON categories_id = c.id
LEFT JOIN users u ON v.users_id = u.id
WHERE 2=2 AND v.status IN ('a','k','f')
ORDER BY likes DESC ) a WHERE a.ustatus IN ('a') LIMIT 36, 12;
You just need to avoid the * selection for tables and fields that are not needed. Also there are overlapping fields in the tables and by itself it causes an issue. I will try to see If I am managing to somehow find a more optimal way to run the query but you will need to change the way you run queries in the system if you want it to be able to run properly on a loaded system...
- v.categories_id [V]
- v.users_id [V]
- v.status [V]
- v.likes [V]
- v.dislikes [V]
- u.status [V]
@DanielnetoDotCom just to make sense about my claim, if you will run the exact same query but verbally use the fields names like in the next query:
SELECT * FROM (SELECT u.id as uid , u.user as uuser , u.name as uname , u.email as uemail ,
u.password as upassword , u.created as ucreated , u.modified as umodified , u.isAdmin as uisAdmin , u.status as ustatus ,
u.photoURL as uphotoURL , u.lastLogin as ulastLogin , u.recoverPass as urecoverPass , u.backgroundURL as ubackgroundURL ,
u.canStream as ucanStream , u.canUpload as ucanUpload , u.canCreateMeet as ucanCreateMeet , u.canViewChart as ucanViewChart , u.about as uabout ,
u.channelName as uchannelName , u.emailVerified as uemailVerified , u.analyticsCode as uanalyticsCode , u.externalOptions as uexternalOptions ,
u.first_name as ufirst_name , u.last_name as ulast_name , u.address as uaddress , u.zip_code as uzip_code , u.country as ucountry ,
u.region as uregion , u.city as ucity , u.donationLink as udonationLink , u.extra_info as uextra_info ,
u.phone as uphone , u.is_company as uis_company ,
v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description,
v.created as videoCreation, v.modified as videoModified
FROM videos as v
LEFT JOIN categories c ON categories_id = c.id
LEFT JOIN users u ON v.users_id = u.id
WHERE 2=2 AND v.status IN ('a','k','f')
ORDER BY likes DESC ) a WHERE a.ustatus IN ('a') LIMIT 1000, 900;
The result would be pretty fast... (ms fast)
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 8.047324959,
"table": {
"table_name": "u",
"access_type": "ref",
"possible_keys": ["PRIMARY", "users_status_IDX"],
"key": "users_status_IDX",
"key_length": "1",
"used_key_parts": ["status"],
"ref": ["const"],
"r_loops": 1,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.045761237,
"r_other_time_ms": 0.0201165,
"filtered": 100,
"r_filtered": 100,
"index_condition": "u.`status` = 'a'"
},
"table": {
"table_name": "v",
"access_type": "ref",
"possible_keys": [
"fk_videos_users_idx",
"video_status_idx",
"videos_status_index"
],
"key": "fk_videos_users_idx",
"key_length": "4",
"used_key_parts": ["users_id"],
"ref": ["AVideo.u.id"],
"r_loops": 1,
"rows": 391299,
"r_rows": 1900,
"r_table_time_ms": 6.939235389,
"r_other_time_ms": 1.010973728,
"filtered": 50.00025558,
"r_filtered": 100,
"attached_condition": "v.`status` in ('a','k','f')"
},
"table": {
"table_name": "c",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"ref": ["AVideo.v.categories_id"],
"r_loops": 1900,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.007669714,
"r_other_time_ms": 0.001959112,
"filtered": 100,
"r_filtered": 100
}
}
}
And the above ANALYZE FORMAT=JSON would result in a query without a temporary table which costs a lot in terms of speed and in general disk access.
Take into account that the whole DB and the indexes actually sits in a 16GB RAM memory system so...
I will think about publishing a dump of the DB just so it could be used as a learning material for SQL queries composition.
I believe that there is 0 DB in this size laying around on the net for learning purposes.
@DanielnetoDotCom OK so just so you and maybe other developers can work with the DATA I have dumped the DB(removed password): https://www.ngtech.co.il/static/AVideo.sql.gz or https://cloud1.ngtech.co.il/static/AVideo.sql.gz which one that works for you faster
I believe it should give you some better understanding on what I do or do not have... Just as a side note, I have seen some post on a good spec for an AVideo machine with 64GB ram and 40+ CPUs and it was .. insane... even with such juice the service won't be able to render a basic page (tested...)
Thanks, eliezer
@DanielnetoDotCom I posted about the issue in the MariaDB mailing list and the link to the thread is: https://lists.launchpad.net/maria-discuss/msg06239.html
it seems that the optimized of both MySQL and MariaDB is unable to compose the right way to do the query. There is a solution by changing the query (not something trivial) and there is a bug report to file to make sure that the issue will be resolved by the SQL queries optimizer. I have not tested the DB in another engine else them MySQL and MariaDB but from my experience probably MSSQL, Oracle and PostgreSQL will not have the same issue.
@DanielnetoDotCom I managed to improve the main problematic queries. There is however one query which makes it a bit problematic to run since it uses lots of ram for every single page browsing: https://github.com/WWBN/AVideo/blob/master/objects/video.php#L1680
An example query which runs very slow is:
SELECT v.users_id, v.type, v.id, v.title,v.description, c.name as category
FROM videos v
LEFT JOIN categories c ON categories_id = c.id
LEFT JOIN users u ON v.users_id = u.id
WHERE 1=1
AND u.status = 'a' AND v.status IN ('a','k','f');
The reason for the memory consumption is too much information that is being transferred from the DB and being used for some reason. In my use case every single page I am browsing in the gallery and refreshing results in a resident consumption of 800+ MB from PHP. Unless there is a reason to use every piece of the selected info it can be optimized to:
SELECT v.id
FROM videos v
LEFT JOIN categories c ON categories_id = c.id
LEFT JOIN users u ON v.users_id = u.id
WHERE 1=1
AND u.status = 'a' AND v.status IN ('a','k','f');
Which in my case will consume 60MB compared to 800+.
It will also reduce the run time of the query from 5-8 seconds to 1.5 seconds.
I don't know what this query is there for so I cannot say if there is a reason that all the selected data is there for. Since it runs every single page browsing and refreshing in the gallery to probably know the COUNT of the videos it's probably preferable to either use a real MariaDB/MySQL View or write a specific function for that. Also it's possible to store some of the video counters in cache which will reduce many queries from 5+ Seconds to less then a 100 ms.
@DanielnetoDotCom can we try to optimize this somehow?
diff --git a/objects/video.php b/objects/video.php
index 51946b8bb..7fc1d41b2 100644
--- a/objects/video.php
+++ b/objects/video.php
@@ -748,7 +748,7 @@ if (!class_exists('Video')) {
}
}
_mysql_connect();
- $sql = "SELECT u.*, v.*, "
+ $sql = "SELECT STRAIGHT_JOIN u.*, v.*, "
. " nv.title as next_title,"
. " nv.clean_title as next_clean_title,"
. " nv.filename as next_filename,"
@@ -1097,7 +1097,7 @@ if (!class_exists('Video')) {
}
$status = str_replace("'", "", $status);
- $sql = "SELECT u.*, v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified "
+ $sql = "SELECT STRAIGHT_JOIN u.*, v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified "
//. ", (SELECT count(id) FROM likes as l where l.videos_id = v.id AND `like` = 1 ) as likes "
//. ", (SELECT count(id) FROM likes as l where l.videos_id = v.id AND `like` = -1 ) as dislikes "
. " FROM videos as v "
@@ -1646,7 +1646,8 @@ if (!class_exists('Video')) {
}
}
- $sql = "SELECT v.users_id, v.type, v.id, v.title,v.description, c.name as category {$cn} "
+// $sql = "SELECT STRAIGHT_JOIN v.users_id, v.type, v.id, v.title,v.description, c.name as category {$cn} "
+ $sql = "SELECT STRAIGHT_JOIN v.id, c.name as category {$cn} "
. "FROM videos v "
. "LEFT JOIN categories c ON categories_id = c.id "
. " LEFT JOIN users u ON v.users_id = u.id "
The above diff is making great things.... from 12 Seconds to less then 3 the worst case.
The specific diff of: + $sql = "SELECT STRAIGHT_JOIN v.id, c.name as category {$cn} " us a test I am running to prevent a DOS attack on the memory usage of the service. I don't know effect it will have and on what but at-least the service is running and the php service is holding up for now without killing running processes and also the service is not failing.
I have couple services that I am testing and it seems that two of three VM's just powered off at some point because of an unknown issue.
This led me to re-think what happen by the way.
Hi, thanks for your help, that is something that I definitely will look into
- Looks like for you you already detect 3 queries
- are you saying just adding STRAIGHT_JOIN already improves a lot the query?
- what about lets focus on only one query now? (maybe the easiest to fix?)
FYI, I just add the STRAIGHT_JOIN in those queries and seems to not break anything. I will make more tests this weekend. if works fine I will release it next week
Unfortunately I do not have the metrics to tell you if it improves or not
I probably need to review all field's names. that is basically the main query for videos.
FYI, I just add the STRAIGHT_JOIN in those queries and seems to not break anything. I will make more tests this weekend. if works fine I will release it next week
Unfortunately I do not have the metrics to tell you if it improves or not
The main issue is that the MariaDB and MySQL query optimizer is doing something wrong. The partial solution for now is to change to STRAIGHT_JOIN. I will add the metrics later of.
About the query at: https://github.com/WWBN/AVideo/blob/master/objects/video.php#L1680
it's possible that the gallery doesn't need such a function to know to how many pages the whole list should have. There should be a COUNT query which should response with only the number of the videos to be able to know how many pages needed in the gallery. About the nice dashboard which shows the number of videos and other counters, it needs an admin level query which should be privileged. I believe that a query which dumps the whole videos table into memory should be restricted for admins and for admins only usage. The regular user shouldn't have any privilege to consume that much RAM and CPU in one query. It's programming/CS basic rules.
Hi, are you talking about Videos::getAllVideosLight method?
so instead of using SELECT v.* I should change to SELECT v.field1, v.field2 v.field3, etc?
@DanielnetoDotCom I am talking about the: getTotalVideos https://github.com/WWBN/AVideo/blob/master/objects/video.php#L1684
There was a change in master...
To determine the number of all videos ie a SUM/COUNT you don't need any field at all so a v.id would be enough with a COUNT(v.id) and it will return the number of videos. I don't know what is the purpose of getTotalVideos so I cannot recommend but a simple change of v.* to the named fields changes the way that the optimizer optimize the query. The best solution would be to fix the DB or use a DB that does the job as it should, however it's not really what we are going to do for a running system... for now...
The getTotalVideos functions is consuming too much memory and from my tests it seems that there is no real reason for that. I managed to lower the memory consumption and runtime from 20++ seconds to less then 15.
You can take a peek at: https://tube.ngtech.co.il/ and at: https://tube.ngtech.co.il/page/2 https://tube.ngtech.co.il/page/3 ... there are 200k+ videos so pick a page randomly to see the change. I had a glitch in my videos update script and it got to the point which I had about 1.2 Million videos instead of 200k and the system was so moving so slow that I started wondering what might cause this issue. The actual issue is not only the query itself but the amount of data that the PHP process just to know how many pages there should be and what is the next page etc etc is a bit weird to me. Either the plugins/code does something wrong or using the wrong function or the function is being abused or couple other options which I am not sure about but I hoped you would have more clue then me what might be needed to be change in the code to make the system faster for 1.2 Million videos.
I must say thanks! It's really an amazing piece of work you have here.
FYI, I just add the STRAIGHT_JOIN in those queries and seems to not break anything. I will make more tests this weekend. if works fine I will release it next week
Unfortunately I do not have the metrics to tell you if it improves or not
You should see these in the php-fpm logs (if you are using php-fpm). I posted the DB dump and I assume you would be able to use it on a local DB VM and see with DBEAVER the results of each query. It would be pretty simple to write a PHP QA test script that will verify how much memory each and every function call consumes on my DB dataset.
Again, Thanks! I will try to help as much as I can with QA on my free time.
I am not sure why I did not use the count(v.id) on the total function, but here is an update, I just comment out the old query in case it breaks something
@DanielnetoDotCom thanks. Look at the next diff that makes the process work even faster:
diff --git a/objects/video.php b/objects/video.php
index 51946b8bb..ac90769ab 100644
--- a/objects/video.php
+++ b/objects/video.php
@@ -748,7 +748,7 @@ if (!class_exists('Video')) {
}
}
_mysql_connect();
- $sql = "SELECT u.*, v.*, "
+ $sql = "SELECT STRAIGHT_JOIN u.*, v.*, "
. " nv.title as next_title,"
. " nv.clean_title as next_clean_title,"
. " nv.filename as next_filename,"
@@ -1097,7 +1097,7 @@ if (!class_exists('Video')) {
}
$status = str_replace("'", "", $status);
- $sql = "SELECT u.*, v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified "
+ $sql = "SELECT STRAIGHT_JOIN u.*, v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified "
//. ", (SELECT count(id) FROM likes as l where l.videos_id = v.id AND `like` = 1 ) as likes "
//. ", (SELECT count(id) FROM likes as l where l.videos_id = v.id AND `like` = -1 ) as dislikes "
. " FROM videos as v "
@@ -1646,7 +1646,7 @@ if (!class_exists('Video')) {
}
}
- $sql = "SELECT v.users_id, v.type, v.id, v.title,v.description, c.name as category {$cn} "
+ $sql = "SELECT STRAIGHT_JOIN v.users_id, v.type, v.id, v.title,v.description, c.name as category {$cn} "
. "FROM videos v "
. "LEFT JOIN categories c ON categories_id = c.id "
. " LEFT JOIN users u ON v.users_id = u.id "
the STRAIGHT_JOIN operator lower the latency from 2.5 seconds to 0.5 seconds. it's worth to add these to make sure the queries will work faster. I changed it currently on my production environment with the latest master.
As example: https://tube.ngtech.co.il/page/9
or choose any number to just test it. The upload speed of my server is very low but:
### Before
[18-Jul-2022 20:27:52 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getInfo getStatistcs] On Line 1389 takes 0.2642 seconds to complete, Limit (0.1). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:27:52 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos foreach] On Line 1323 takes 0.8549 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:27:54 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [[SELECT u.*, v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified FROM videos as v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 2=2 AND u.status = 'a' AND v.status IN ('a','k','f') ORDER BY v.views_count DESC LIMIT 48, 12 ], , [], ] On Line mysql_dal takes 1.7153 seconds to complete, Limit (0.5). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:27:54 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos] On Line 1275 takes 1.7166 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:27:55 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos foreach] On Line 1323 takes 0.554 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:27:57 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [[SELECT u.*, v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified FROM videos as v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 2=2 AND u.status = 'a' AND v.status IN ('a','k','f') ORDER BY likes DESC LIMIT 48, 12 ], , [], ] On Line mysql_dal takes 1.7032 seconds to complete, Limit (0.5). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:27:57 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos] On Line 1275 takes 1.7047 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:30:58 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [[SELECT STRAIGHT_JOIN v.users_id, v.type, v.id, v.title,v.description, c.name as category FROM videos v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 1=1 AND u.status = 'a' AND v.status IN ('a','k','f')], , [], ] On Line mysql_dal takes 1.1232 seconds to complete, Limit (0.5). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:30:59 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos] On Line 1275 takes 0.4365 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:00 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos foreach] On Line 1323 takes 0.513 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
###
### After::
[18-Jul-2022 20:31:09 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [[SELECT STRAIGHT_JOIN v.users_id, v.type, v.id, v.title,v.description, c.name as category FROM videos v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 1=1 AND u.status = 'a' AND v.status IN ('a','k','f')], , [], ] On Line mysql_dal takes 1.1148 seconds to complete, Limit (0.5). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:09 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227629, SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:09 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227629 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:10 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos] On Line 1275 takes 0.436 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:10 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227629, SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:10 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227629 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:10 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227440, EE:EE:EE SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:10 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227440 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:10 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227441, EE:EE:EE SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:10 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227441 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:11 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227437, EE:EE:EE SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:11 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227437 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:11 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos foreach] On Line 1323 takes 0.3545 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:12 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos foreach] On Line 1323 takes 0.5362 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:12 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227629, SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:12 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227629 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:25 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [[SELECT STRAIGHT_JOIN v.users_id, v.type, v.id, v.title,v.description, c.name as category FROM videos v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 1=1 AND u.status = 'a' AND v.status IN ('a','k','f')], , [], ] On Line mysql_dal takes 1.1072 seconds to complete, Limit (0.5). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:25 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227441, EE:EE:EE SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:25 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227441 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:25 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227440, EE:EE:EE SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:25 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227440 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:25 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227437, EE:EE:EE SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:25 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227437 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:26 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos] On Line 1275 takes 0.4341 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:27 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos foreach] On Line 1323 takes 0.3852 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:27 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos foreach] On Line 1323 takes 0.3237 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:27 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227441, EE:EE:EE SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:27 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227441 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:27 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227440, EE:EE:EE SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:27 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227440 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:27 Asia/Jerusalem] AVideoLog::DEBUG: Video::updateDurationInSeconds empty duration 1227437, EE:EE:EE SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:27 Asia/Jerusalem] AVideoLog::DEBUG: Video duration_in_seconds not updated: id=1227437 type=embed SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:42 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [[SELECT STRAIGHT_JOIN v.users_id, v.type, v.id, v.title,v.description, c.name as category FROM videos v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 1=1 AND u.status = 'a' AND v.status IN ('a','k','f')], , [], ] On Line mysql_dal takes 1.1283 seconds to complete, Limit (0.5). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:43 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos foreach] On Line 1323 takes 0.2784 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:43 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos] On Line 1275 takes 0.4383 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
[18-Jul-2022 20:31:44 Asia/Jerusalem] AVideoLog::DEBUG: Warning: Slow process detected [video::getAllVideos foreach] On Line 1323 takes 0.3452 seconds to complete, Limit (0.2). /var/www/AVideo/view/index.php SCRIPT_NAME: /view/index.php
###
Should be convincing enough that it's worth it.
Awesome, also I can see still some queries that we can add the STRAIGHT_JOIN.
For example: SELECT u.*, v.*, c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description
I am adding it now, and I hope it is worth even more.
thanks for your help
@DanielnetoDotCom You are welcome. It works in production for the last month and seems to be pretty stable ob both MySQL and MariaDB. The #6956 issue might not be related to this issue at all.