maria-discuss team mailing list archive

[Gordan Bobic <gordan.bobic@xxxxxxxxx>]

It isn't the same query.
Sub-selects tend to execute by fully constructing the data set,
putting it into a temporary table, and then selecting from that for
the wrapping query. That means you get no benefit from early stopping,
and often the optimizer doesn't figure out the appropriate indexing so
your temporary table ends up with a full table scan.
If you care about performance, avoid sub-selects by re-writing the
query without it. If sub-selects are unavoidable, use an appropriately
indexed materialized view (not a regular view, a regular view is just
a sub-select in disguise in the vast majority of cases) that you can
either refresh periodically via a scheduled event, or if necessary in
real-time using triggers on the underlying tables.


On Wed, Jul 13, 2022 at 9:25 AM <ngtech1ltd@xxxxxxxxx> wrote:
>
> The full DB can be downloaded from:
> https://www.ngtech.co.il/static/AVideo.sql.gz
>
> Thanks for any advice.
> 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/
>
> -----Original Message-----
> From: ngtech1ltd@xxxxxxxxx <ngtech1ltd@xxxxxxxxx>
> Sent: Wednesday, 13 July 2022 8:46
> To: 'maria-discuss@xxxxxxxxxxxxxxxxxxx' <maria-discuss@xxxxxxxxxxxxxxxxxxx>
> Subject: Looking for advice where to start looking at a slow query case
>
> 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/
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp