Hey,
I have tried to run a local service which claims to be a YouTube alike which is
named AVideo(used to be PHPTube).
I loaded the service with 220k+ videos from YouTube and have used MariaDB as
the DB backend.
The service moved slowly as the size of the videos DB growed from 100k to 150k.
I got a recommendation from the developer to use MySQL which didn’t made any
sense to me.
I tracked the issue to the SQL queries that the service runs.
Currently I am upgrading my local servers so the DB cannot be downloaded for
testing however I have seen a very interesting thing.
I will first put the query here:
```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
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;
```
The ANALYZE FORMAT=JSON for that query is:
```json
{
"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
}
}
}
}
}
```
While for the next:
```sql
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 ANALYZE FORMAT=JSON is the next:
```json
{
"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 what I am trying to understand is, what's the difference? Why would the
same exact query will result with this issue?
Just to notice that the next query will not run at all due to fields naming
conflict between u and v:
```sql
SELECT * FROM (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 v.status IN ('a','k','f')
ORDER BY likes DESC ) a WHERE a.ustatus IN ('a') LIMIT 1000, 900;
```
I am not new to the DB world that much but it's the first time I am getting my
hands dirty about such an issue.
I will share the full DB later on today so it could be used as a learning
material.
Also, are there any big public databases that can be used for learning and
testing purposes?
Thanks,
Eliezer
----
Eliezer Croitoru
NgTech, Tech Support
Mobile: +972-5-28704261
Email: [email protected]
Web: https://ngtech.co.il/
My-Tube: https://tube.ngtech.co.il/
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
