On this article you’ll be able to be taught:
Easy methods to Enhance the Velocity of An Software that Makes use of a MySQL Database Server
1. Earlier Article: Discover MySQL Sluggish Queries by Activating the Sluggish Question Log
5. This Article: Easy methods to Assist the EverSQL Device Optimizing MySQL SQL Queries Extra Effectively by Passing It Your MySQL Database Metadata
6. Subsequent Article: Easy methods to Optimize MySQL SQL Queries with the EverSQL Device
Contents
Easy methods to Assist the EverSQL Device Optimizing MySQL SQL Queries Extra Effectively by Passing It Your MySQL Database Metadata
1. Easy methods to Get a SQL Question to Extract MySQL Database Metadata to Assist EverSQL Device to Perceive Your Database Desk Schema
Let’s concentrate on the precise SQL question optimization.
You simply click on right here and you progress on to a different display screen of this instrument on which it asks you for some particulars about that question.
We’re going right here. You need to use the Proceed button.
Let’s pause right here as a result of we have to determine some steps that it is advisable to do earlier than the precise optimization.
You click on exterior of that dialog and you’ve got right here a perform that’s essential referred to as Replace database metadata.
This perform is helpful to inform the EverSQL instrument in regards to the database schema so it understands higher the database construction and might counsel good optimizations on your database the tables.
You click on right here and you progress on to a different window that has a dialog that may go you a SQL question that you need to use to execute and extract that database metadata.
You click on right here on the copy SQL button.
Will probably be copied to the clipboard and you then go on our favourite textual content editor and paste that database question such as you see right here.
(SELECT CAST(CONCAT('{"columns": [',IFNULL(@cols,''),'], "indexes": [',IFNULL(@indexes,''),'], "tables":[',IFNULL(@tbls,''),'], "server_name": "', @@hostname, '", "model": "', VERSION(), '"}') AS CHAR) as metadata_json FROM (SELECT (@cols:=NULL), (SELECT (0) FROM information_schema.columns cols WHERE table_schema LIKE IFNULL(NULL, '%') and table_schema not in ('sys', 'performance_schema', 'information_schema') AND (0x00) IN (@cols:=CONCAT_WS(',', @cols, CONCAT('{"schema":"',cols.table_schema,'","desk":"',cols.table_name,'","identify":"', change(cols.column_name,'"', '"'), '","kind":"', cols.column_type, '","nullable":', IF(cols.IS_NULLABLE = 'YES', 'true', 'false'), ',"collation":"', IFNULL(cols.COLLATION_NAME, ''), '"}')))) ) cols, (SELECT (@indexes:=NULL), (SELECT (0) FROM information_schema.statistics indexes WHERE table_schema LIKE IFNULL(NULL, '%') and table_schema not in ('sys', 'performance_schema', 'information_schema') AND (0x00) IN (@indexes:=CONCAT_WS(',', @indexes, CONCAT('{"schema":"',indexes.table_schema,'","desk":"',indexes.table_name,'","identify":"', indexes.index_name, '","column":"', indexes.column_name, '","index_type":"', LOWER(indexes.index_type), '","cardinality":', indexes.cardinality, ',"distinctive":', IF(indexes.non_unique = 1, 'false', 'true'), '}')))) ) indexes, (SELECT (@config:=NULL), (SELECT (0) FROM information_schema.global_variables config WHERE (0x00) IN (@config:=CONCAT_WS(',', @config, CONCAT('{', '"identify":"', `VARIABLE_NAME`, '",', '"worth":"', change(change(`VARIABLE_VALUE`,'','\'),'"', '"'), '"}')))) ) config, (SELECT (@tbls:=NULL), (SELECT (0) FROM information_schema.tables tbls WHERE table_schema LIKE IFNULL(NULL, '%') and table_schema not in ('sys', 'performance_schema', 'information_schema') AND (0x00) IN (@tbls:=CONCAT_WS(',', @tbls, CONCAT('{', '"schema":"', `TABLE_SCHEMA`, '",', '"desk":"', `TABLE_NAME`, '",', '"rows":', IFNULL(`TABLE_ROWS`, 0), ', "kind":"', IFNULL(`TABLE_TYPE`, ''), '",', '"engine":"', IFNULL(`ENGINE`, ''), '",', '"collation":"', IFNULL(`TABLE_COLLATION`, ''), '"}')))) tbls) x);
It is advanced. I am not going to elucidate this. It is not essential. You simply must execute this question to get the database metadata.
Then you definitely save this SQL question to a file. I referred to as it database metadata.sql.
2. Easy methods to Execute the SQL Question to Extract the MySQL Database Metadata
mlemos@improvement:~> mysql -N -u mlemos -p phpclasses <database-metadata.sql >database-metadata.txt Enter password: mlemos@improvement:~>
Then you definitely transfer on to the following step on which you’ll execute this question, so it will probably output the results of that question to a file named database-metadata.txt.
So that is the output. That is what the EverSQL instrument wants.
3. Easy methods to Submit the MySQL Database Metadata File to the EverSQL Device
Then you definitely get again to the this instrument. You’ll be able to click on right here to add this file.
And also you click on right here to add the file. You specify the file right here after which the instrument might be able to do a greater optimization with the data that’s gathered from the database metadata construction.
