![]() ![]() You can use any size, small/medium/large – they’ll all work to show the problems.Īfter this session, your next steps for learning are: I’ll be using the Stack Overflow public database. Show you my favorite tips to make it faster and easier to troubleshoot (and note that in the live class, I don’t cover all of the tips – the script keeps going with my conversational style in the comments, so you can read through more tips & tricks after class).Build a multi-parameter stored procedure the old-fashioned way.It can also be a route to sitting under your desk, banging your head against a wine bottle, wishing you could stop seeing double apostrophes mocking you. It can be an awesome, powerful, and fast solution to a lot of performance problems. ![]() Note I removed the DISTINCT as it should be unnecessary, and perhaps a LIMIT 1 on the SELECT (SELECT)'s would make it perfectly safe.If you’ve ever needed to build a stored procedure that took a lot of different parameters and served a lot of different purposes, you’ve probably used dynamic SQL. Information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND i.TABLE_SCHEMA = k.TABLE_SCHEMA (SELECT c.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS c WHERE c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.CONSTRAINT_SCHEMA = k.TABLE_SCHEMA) DELETE_RULE, (SELECT c.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS c WHERE c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.CONSTRAINT_SCHEMA = k.TABLE_SCHEMA) UPDATE_RULE, However I did notice that its the final join to REFERENTIAL_CONSTRAINTS where everything falls apart so this oddity actually works perfectly and I think still meets the need, now running in less than 1 second: So I tried all sorts of the usual optimisations, filtered subqueries, even CTE's in MySQL 8, but all to no avail. So with the updated query I was still getting poor performance of around 50 seconds □. This query with the schema returns in 3.5s for me. SELECT DISTINCT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, k.COLUMN_NAME, c.UPDATE_RULE, c.DELETE_RULE, k.ORDINAL_POSITION FROM information_schema.TABLE_CONSTRAINTS i INNER JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND i.table_schema = k.table_schema INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS c ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.constraint_schema = k.table_schema WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' AND i.TABLE_SCHEMA = DATABASE() ORDER BY k.ORDINAL_POSITION ASC However I think you actually need to optimise the query to pass through the table schema name in addition, i.e. I get that its probably doing a multiplier but even then I thought it should be slow but still return fairly fast anyways. SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS - 20,162 SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE - 21,738 SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS - 11,135 ![]() The tables in question aren't really that big and we have a large server so I dont know what MySQL is doing wrong: REFERENTIAL_CONSTRAINTS c ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' AND i.TABLE_SCHEMA = DATABASE() ORDER BY k.ORDINAL_POSITION ASC SELECT DISTINCT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, k.COLUMN_NAME, c.UPDATE_RULE, c.DELETE_RULE, k.ORDINAL_POSITION FROM information_schema.TABLE_CONSTRAINTS i INNER JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME INNER JOIN information_schema. With the latest release Version 2022.37 (Build 110905.5) and RDS MySQL 8.0.29 I noticed a new issue which is this query SQLPro is sending doesn't seem to return in a reasonable time (I give up it takes so long, 1,000's of seconds waited). ![]() As a background we have a lot of schemas that are the same, over 100, on our database. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |