maria-discuss team mailing list archive

[<ngtech1ltd@xxxxxxxxx>]

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: ngtech1ltd@xxxxxxxxx
Web: https://ngtech.co.il/
My-Tube: https://tube.ngtech.co.il/