maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #06243
Re: Looking for advice where to start looking at a slow query case
Ok, thanks.
בתאריך יום ד׳, 13 ביולי 2022, 10:42, מאת 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
>
References
