PHP Classes

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

Recommend this page to a friend!
  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog How to Help the EverS...   Post a comment Post a comment   See comments See comments (2)   Trackbacks (0)  

Author:

Viewers: 150

Last month viewers: 2

Categories: PHP Tutorials, Lately in PHP Podcast

The EverSQL tool is excellent for suggesting optimizations for SQL queries for your applications that use MySQL.

That tool must first obtain the structure of your database tables to suggest good optimizations. You can get that structure by executing a SQL query that extracts the database metadata.

Read this article, watch a 4-minute video, or listen to part 4 of episode 93 of the Lately in PHP podcast to learn how to extract your MySQL database metadata to help the EverSQL tool to optimize your application SQL queries for free.




Loaded Article

In this article you can learn:

How to Improve the Speed of An Application that Uses a MySQL Database Server

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


Listen or download the podcast, RSS feed and subscribe in iTunes

Click on the Play button to listen now.


Download Size: 1MB Listeners: 725

Introduction music obtained with permission from: http://spoti.fi/NCS

View Podcast in iTunes

Listen on Spotify
Listen on Spotify


Sound effects obtained with permission from: https://www.zapsplat.com/

In iTunes, use the Subscribe to Podcast... item of the Advanced menu, and then enter the URL above to subscribe to this podcast.

Watch the podcast video

See the Lately in PHP podcast play list on YouTube and Subscribe to this channel there.

Episode 93 Part 5 Video

What was said in the podcast

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.

EverSQL Update database metadata button

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




You need to be a registered user or login to post a comment

1,614,395 PHP developers registered to the PHP Classes site.
Be One of Us!

Login Immediately with your account on:



Comments:

1. missing somme examples - José Filipe Lopes Santos (2022-11-17 10:53)
missing somme examples... - 1 reply
Read the whole comment and replies



  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog How to Help the EverS...   Post a comment Post a comment   See comments See comments (2)   Trackbacks (0)