How to Help the EverSQL Tool Optimizing MySQL SQL Queries More Efficiently by Passing It Your MySQL Database Metadata - 4 minutes - Lately in PHP Podcast Episode 93 Part 5

In this article you can learn:

1. Previous Article: Find MySQL Slow Queries by Activating the Slow Query Log

5. This Article: How to Help the EverSQL Tool Optimizing MySQL SQL Queries More Efficiently by Passing It Your MySQL Database Metadata

6. Next Article: How to Make SQL Queries Run Faster Using the EverSQL MySQL Query Optimization Tool

Contents

How to Help the EverSQL Tool Optimizing MySQL SQL Queries More Efficiently by Passing It Your MySQL Database Metadata

1. How to Get a SQL Query to Extract MySQL Database Metadata to Help EverSQL Tool to Understand Your Database Table Schema

Let's focus on the actual SQL query optimization.

You just click here and you move on to another screen of this tool on which it asks you for some details about that query. 

We are going here. You can use the Continue button.

Let's pause here because we need to figure some steps that you need to do before the actual optimization.

You click outside of that dialog and you have here a function that is important called Update database metadata.

This function is useful to tell the EverSQL tool about the database schema so it understands better the database structure and can suggest good optimizations for your database the tables.

You click here and you move on to another window that has a dialog that can pass you a SQL query that you can use to execute and extract that database metadata.

You click here on the copy SQL button. 

It will be copied to the clipboard and then you go on our favorite text editor and paste that database query like you see here.

(SELECT CAST(CONCAT('{"columns": [',IFNULL(@cols,''),'], "indexes": [',IFNULL(@indexes,''),'], "tables":[',IFNULL(@tbls,''),'], "server_name": "', @@hostname, '", "version": "', 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,'","table":"',cols.table_name,'","name":"', replace(cols.column_name,'"', '\\"'), '","type":"', 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,'","table":"',indexes.table_name,'","name":"', indexes.index_name, '","column":"', indexes.column_name, '","index_type":"', LOWER(indexes.index_type), '","cardinality":', indexes.cardinality, ',"unique":', 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('{', '"name":"', `VARIABLE_NAME`, '",', '"value":"', replace(replace(`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`, '",', '"table":"', `TABLE_NAME`, '",', '"rows":', IFNULL(`TABLE_ROWS`, 0), ', "type":"', IFNULL(`TABLE_TYPE`, ''), '",', '"engine":"', IFNULL(`ENGINE`, ''), '",', '"collation":"', IFNULL(`TABLE_COLLATION`, ''), '"}')))) tbls) x);

It's complex. I'm not going to explain this. It's not important. You just need to execute this query to get the database metadata.

Then you save this SQL query to a file. I called it database metadata.sql.

2. How to Execute the SQL Query to Extract the MySQL Database Metadata

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

Then you move on to the next step on which you will execute this query, so it can output the result of that query to a file named database-metadata.txt.

So this is the output. This is what the EverSQL tool needs.

3. How to Submit the MySQL Database Metadata File to the EverSQL Tool

Then you get back to the this tool. You can click here to upload this file.

And you click here to upload the file. You specify the file here and then the tool will be ready to do a better optimization with the knowledge that is gathered from the database metadata structure.

Show notes

• EverSQL SQL Optimizer Tool