USAGE: ./mysql-index-analyzer.sh YOUR_DATABASE

mysql-index-analyzer.sh

#!/bin/sh

#define you database user:
USR="root"
#define your database password:
PWD=""

formatt () {
  LINES=$1
  FLINE=$(echo "$LINES"|head -1)
  NF=$(echo "$FLINE"|awk '{print NF}')
  NL=$(echo "$LINES"|wc -l)
  WORDS=(`echo $FLINE`)
  CNT=0

  while [ $CNT -lt $NF ]; do
    WORD=${WORDS[$CNT]}
    FIELDS[$CNT]=${#WORD}
    CNT=`expr $CNT + 1`
  done

  CNT2=2 #skipping firt line
  while [ $CNT2 -le $NL ]; do
    LINE=`echo "$LINES"|sed -n "${CNT2}p"`
    WORDS=($LINE)
    CNT=0

    while [ $CNT -lt $NF ]; do
      WORD=${WORDS[$CNT]}
      WL=${#WORD}
      if [ ${FIELDS[$CNT]} -lt $WL ]; then FIELDS[$CNT]=$WL; fi
      CNT=`expr $CNT + 1`
    done
    CNT2=`expr $CNT2 + 1`
  done

  CNT=0
  LINLEN=0
  while [ $CNT -lt $NF ]; do LINLEN=$(expr $LINLEN + ${FIELDS[$CNT]}); CNT=$(expr $CNT + 1); done
  LINLEN=$(echo "$LINLEN + 1 + $NF * 2"|bc)
  LINESEP=$(printf "%*s" $LINLEN "")
  LINESEP=$(echo "${LINESEP// /-}")


  echo $LINESEP
  CNT2=1 #skipping firt line
  while [ $CNT2 -le $NL ]; do
    if [ $CNT2 -eq 2 ]; then echo $LINESEP; fi
    LINE=`echo "$LINES"|sed -n "${CNT2}p"`
    WORDS=($LINE)
    CNT=0

    while [ $CNT -lt $NF ]; do
      WORD=${WORDS[$CNT]}
      if [ -z $WORD ]; then WORD="--"; fi
      if [ $CNT -eq 0 ]; then echo "|\c"; fi
      echo "$(echo $WORD | sed -e :a -e "s/^.\{1,${FIELDS[$CNT]}\}$/& /;ta")\c"
      echo "|\c"
      CNT=`expr $CNT + 1`
    done
    echo ""

    CNT2=`expr $CNT2 + 1`
  done
  echo $LINESEP
}

OUT=$(mysql -u root -p$PWD $1 -e 'SHOW TABLES;'| sed '1d')
for table in $OUT
do
  formatt "$(mysql -u$USR -p$PWD $1 -e "SHOW COLUMNS FROM $table;")"
  echo ""
  formatt "$(mysql -u$USR -p$PWD $1 -e "SHOW INDEX FROM $table;")"
  echo ""
done