Procedure Analyse

• Tweet
• 

        // show statistics and propose an optimal data type for the columns. Compare the results with the create table statement.

-- show statistics and propose an optimal data type for the columns
SELECT * FROM my_db.my_table PROCEDURE ANALYSE()\G

-- Compare the results with the create table 
SHOW CREATE TABLE my_db.my_table\G

-- following shell script can be used to analyse all the tables

#!/bin/sh

cat > procedure_run.sh << "here_doc"

#!/bin/bash
filename=${1:-max}
max=proc_$filename

> $max.txt
while read tblname ; do

echo "########################################## " >> $max.txt
echo "optimization report for the table $tblname " >> $max.txt
echo "########################################## " >> $max.txt
echo "create table " >> $max.txt
mysql -uroot -proot@123 -e"show create table $tblname\G" >> $max.txt

echo "########################################## " >> $max.txt
echo "sample data " >> $max.txt

mysql -uroot -proot@123 -e"select * from $tblname limit 1\G" >> $max.txt

echo "########################################## " >> $max.txt
echo "procedure analyse()" >> $max.txt

mysql -uroot -proot@123 -e"select * from $tblname procedure analyse()\G" >> $max.txt
done <<HERE
 `mysql -uroot -proot@123 -Be"select CONCAT(TABLE_SCHEMA, '.' , TABLE_NAME) as noname from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$1'"`
HERE

here_doc


while read db_name ; do

sh -xv procedure_run.sh $db_name
done <<db_list
`mysql -uroot -proot@123 -Be"select SCHEMA_NAME from information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'test')"`
db_list

zip procedure.zip proc_*

grep -B3 'Max_value: \(127\|32767\|8388607\|8388607\|2147483647\|255\|65535\|16777215\|4294967295\)' proc_*.*

Tags:
• forge
• MySQL
• oksoft
• shell_script