maria-discuss team mailing list archive

[Gordan Bobic <gordan.bobic@xxxxxxxxx>]

Just had a look with the data set you provided, this looks like a
classical case of query optimizer getting it wrong.

In your first query, do this modification:
s/SELECT/SELECT STRAIGHT_JOIN/
and the execution time goes from seconds to milliseconds.
The optimizer wasn't smart enough to figure out that the optimal plan
is to start with the videos table and take advantage of early stopping
with limit.

It seems reasonably reproducible so you might want to file an
optimizer bug on MariaDB jira.

On Wed, Jul 13, 2022 at 9:59 AM NgTech LTD <ngtech1ltd@xxxxxxxxx> wrote:
>
> Sorry, I didn't got why what works faster is faster.
> Also when I am removing the Order by from the original query it runs faster.
> There is index to the relevant fields.
> The big table is the videos so I assumed that the db engine would be smart enough to first run the search in an optimized fashion and then compare to the other tables by the id.
>
> also why the difference between the same exact query when the u.* is used it's slow but when I am using the AS to define each and every field renaming it would run faster.
> It doesn't make any sense to me as a programmer.
> If I would have written the code to do this query it would be in such a way that it will run in couple ms...
>
> I have started re-learning my DBA materials with hope to JOIN my sysadmin, programming and dba skills to make sense in the future what doesn't make sense now.
>
> Thanks!
>
> בתאריך יום ד׳, 13 ביולי 2022, 9:45, מאת 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