EverSQL utilizes dozens of metadata types to decide what's the best path to continuously optimize your database and queries for optimal performance.
To generate a free initial optimization report for your database, we'll ask you to run one simple SQL query to provide us your database metadata.
What does the metadata includes?
The metadata doesn't include any actual data from your database and will include just:
- Tables metadata: table names, tables size, character sets, engine type.
- Columns metadata: table names, column names, types, nullable props, character sets.
- Indexes metadata: table names, index names, types, columns, cardinality, uniqueness.
- Slow queries: the SQL text, the duration sum, execution timestamps, executions number.
Generate metadata for MySQL 8.x
Please execute the following query using your SQL IDE and send us the JSON output. Please make sure your IDE doesn't trim the output (as it can be few MBs of metadata):
(SELECT CAST(CONCAT('{"columns": [',IFNULL(@cols,''),'], "indexes": [',IFNULL(@indexes,''),'], "tables":[',IFNULL(@tbls,''),'], "queries": [',IFNULL(@queries,''),']}') AS CHAR) as metadata_json FROM (SELECT (@queries:=NULL), (SELECT (0) FROM performance_schema.events_statements_summary_by_digest queries WHERE schema_name like IFNULL(database(), '%') AND (0x00) IN (@queries:=CONCAT_WS(',', @queries, CONCAT('{"schema":"',queries.schema_name,'","query_digest":"',REPLACE(to_base64(queries.query_sample_text), '\n', ''),'","duration_sum":', round(queries.sum_timer_wait/ 1000000000, 6), ',"count":', queries.count_star, ',"first_seen":', CAST(UNIX_TIMESTAMP(queries.first_seen) AS UNSIGNED), ',"last_seen":', CAST(UNIX_TIMESTAMP(queries.last_seen) AS UNSIGNED), '}')))) ) queries, (SELECT (@cols:=NULL), (SELECT (0) FROM information_schema.columns cols WHERE table_schema like IFNULL(database(), '%') AND (0x00) IN (@cols:=CONCAT_WS(',', @cols, CONCAT('{"schema":"',cols.table_schema,'","table":"',cols.table_name,'","name":"', 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(database(), '%') 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 (@tbls:=NULL), (SELECT (0) FROM information_schema.tables tbls WHERE table_schema like IFNULL(database(), '%') AND (0x00) IN (@tbls:=CONCAT_WS(',', @tbls, CONCAT('{', '"schema":"', `TABLE_SCHEMA`, '",', '"table":"', `TABLE_NAME`, '",', '"rows":', `TABLE_ROWS`, ',', '"engine":"', `ENGINE`, '",', '"collation":"', `TABLE_COLLATION`, '"}')))) tbls) x);Generate metadata for MySQL 5.7.7 - 5.7.32
Please execute the following query using your SQL IDE and send us the JSON output. Please make sure your IDE doesn't trim the output (as it can be few MBs of metadata):
(SELECT CAST(CONCAT('{"columns": [', IFNULL(@cols, ''), '], "indexes": [', IFNULL(@indexes, ''), '], "tables":[', IFNULL(@tbls, ''), '], "queries": [', IFNULL(@queries, ''), ']}') AS CHAR) AS metadata_json FROM (SELECT (@queries:=NULL), (SELECT (0) FROM performance_schema.events_statements_summary_by_digest queries WHERE schema_name LIKE IFNULL(DATABASE(), '%') AND (0x00) IN (@queries:=CONCAT_WS(',', @queries, CONCAT('{"schema":"', queries.schema_name, '","query_digest":"', REPLACE(TO_BASE64(queries.DIGEST_TEXT), ' ', ''), '","duration_sum":', ROUND(queries.sum_timer_wait / 1000000000, 6), ',"count":', queries.count_star, ',"first_seen":', CAST(UNIX_TIMESTAMP(queries.first_seen) AS UNSIGNED), ',"last_seen":', CAST(UNIX_TIMESTAMP(queries.last_seen) AS UNSIGNED), '}')))) ) queries, (SELECT (@cols:=NULL), (SELECT (0) FROM information_schema.columns cols WHERE table_schema LIKE IFNULL(DATABASE(), '%') AND (0x00) IN (@cols:=CONCAT_WS(',', @cols, CONCAT('{"schema":"', cols.table_schema, '","table":"', cols.table_name, '","name":"', 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(DATABASE(), '%') 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 (@tbls:=NULL), (SELECT (0) FROM information_schema.tables tbls WHERE table_schema LIKE IFNULL(DATABASE(), '%') AND (0x00) IN (@tbls:=CONCAT_WS(',', @tbls, CONCAT('{', '"schema":"', `TABLE_SCHEMA`, '",', '"table":"', `TABLE_NAME`, '",', '"rows":', `TABLE_ROWS`, ',', '"engine":"', `ENGINE`, '",', '"collation":"', `TABLE_COLLATION`, '"}')))) tbls ) x);Generate metadata for MySQL 5.0 - 5.7.6
Please execute the following query using your SQL IDE and send us the JSON output. Please make sure your IDE doesn't trim the output (as it can be few MBs of metadata):
(SELECT CAST(CONCAT('{"columns": [',IFNULL(@cols,''),'], "indexes": [',IFNULL(@indexes,''),'], "tables":[',IFNULL(@tbls,''),']}') AS CHAR) as metadata_json FROM (SELECT (@cols:=NULL), (SELECT (0) FROM information_schema.columns cols WHERE table_schema not in ('mysql','information_schema', 'sys', 'performance_schema') AND (0x00) IN (@cols:=CONCAT_WS(',', @cols, CONCAT('{"schema":"',cols.table_schema,'","table":"',cols.table_name,'","name":"', 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 not in ('mysql','information_schema', 'sys', 'performance_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 (@tbls:=NULL), (SELECT (0) FROM information_schema.tables tbls WHERE table_schema not in ('mysql','information_schema', 'sys', 'performance_schema') AND (0x00) IN (@tbls:=CONCAT_WS(',', @tbls, CONCAT('{', '"schema":"', `TABLE_SCHEMA`, '",', '"table":"', `TABLE_NAME`, '",', '"rows":', `TABLE_ROWS`, ',', '"engine":"', `ENGINE`, '",', '"collation":"', `TABLE_COLLATION`, '"}')))) tbls) x);Also, if you have a slow query log enabled in MySQL, please zip the files and share them with us for a more enhanced optimization report.