How you can Assist the EverSQL Device Optimizing MySQL SQL Queries Extra Effectively by Passing It Your MySQL Database Metadata – 4 minutes – These days in PHP Podcast Episode 93 Half 5

On this article you possibly can be taught:

How you can Enhance the Pace of An Utility that Makes use of a MySQL Database Server

1. Earlier Article: Discover MySQL Gradual Queries by Activating the Gradual Question Log

5. This Article: How you can Assist the EverSQL Device Optimizing MySQL SQL Queries Extra Effectively by Passing It Your MySQL Database Metadata

6. Subsequent Article: How you can Optimize MySQL SQL Queries with the EverSQL Device

Contents

How you can Assist the EverSQL Device Optimizing MySQL SQL Queries Extra Effectively by Passing It Your MySQL Database Metadata

1. How you can Get a SQL Question to Extract MySQL Database Metadata to Assist EverSQL Device to Perceive Your Database Desk Schema

Let’s give attention to the precise SQL question optimization.

You simply click on right here and you progress on to a different display of this device on which it asks you for some particulars about that question. 

We’re going right here. You should utilize the Proceed button.

Let’s pause right here as a result of we have to determine some steps that it’s good to do earlier than the precise optimization.

You click on outdoors of that dialog and you’ve got right here a operate that’s essential known as Replace database metadata.

This operate is helpful to inform the EverSQL device in regards to the database schema so it understands higher the database construction and might recommend good optimizations in your database the tables.

You click on right here and you progress on to a different window that has a dialog that may cross you a SQL question that you should utilize to execute and extract that database metadata.

You click on right here on the copy SQL button. 

It will likely be copied to the clipboard and then you definately 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,'","title":"', substitute(cols.column_name,'"', '"'), '","sort":"', 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,'","title":"', 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('{', '"title":"', `VARIABLE_NAME`, '",', '"worth":"', substitute(substitute(`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), ', "sort":"', IFNULL(`TABLE_TYPE`, ''), '",', '"engine":"', IFNULL(`ENGINE`, ''), '",', '"collation":"', IFNULL(`TABLE_COLLATION`, ''), '"}')))) tbls) x);

It is complicated. I am not going to clarify this. It isn’t essential. You simply have to execute this question to get the database metadata.

You then save this SQL question to a file. I known as it database metadata.sql.

2. How you can Execute the SQL Question to Extract the MySQL Database Metadata

mlemos@growth:~> mysql -N -u mlemos -p phpclasses <database-metadata.sql >database-metadata.txt
Enter password: 
mlemos@growth:~> 

You then transfer on to the subsequent step on which you’ll execute this question, so it may well output the results of that question to a file named database-metadata.txt.

So that is the output. That is what the EverSQL device wants.

3. How you can Submit the MySQL Database Metadata File to the EverSQL Device

You then get again to the this device. You may 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 device shall be able to do a greater optimization with the information that’s gathered from the database metadata construction.

Present notes