Ok, thanks. בתאריך יום ד׳, 13 ביולי 2022, 10:42, מאת Gordan Bobic < gordan.bo...@gmail.com>:
> 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 <ngtech1...@gmail.com> 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.bo...@gmail.com>: > >> > >> 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 <ngtech1...@gmail.com> 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: ngtech1...@gmail.com > >> > Web: https://ngtech.co.il/ > >> > My-Tube: https://tube.ngtech.co.il/ > >> > > >> > -----Original Message----- > >> > From: ngtech1...@gmail.com <ngtech1...@gmail.com> > >> > Sent: Wednesday, 13 July 2022 8:46 > >> > To: 'maria-discuss@lists.launchpad.net' < > maria-discuss@lists.launchpad.net> > >> > 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: ngtech1...@gmail.com > >> > Web: https://ngtech.co.il/ > >> > My-Tube: https://tube.ngtech.co.il/ > >> > > >> > > >> > > >> > _______________________________________________ > >> > Mailing list: https://launchpad.net/~maria-discuss > >> > Post to : maria-discuss@lists.launchpad.net > >> > Unsubscribe : https://launchpad.net/~maria-discuss > >> > More help : https://help.launchpad.net/ListHelp >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
