62 lines
1.9 KiB
Bash
62 lines
1.9 KiB
Bash
|
#!/bin/bash
|
||
|
|
|
||
|
|
set -e
|
||
|
|
|
||
|
|
if [ -z "$1" ]; then
|
||
|
|
echo "Usage: $0 \"<query>\""
|
||
|
|
echo "Required environment variables: DB_HOST, DB_USER, DB_PASS, DB_NAME"
|
||
|
|
exit 1
|
||
|
|
fi
|
||
|
|
|
||
|
|
if ! command -v mysql &> /dev/null; then
|
||
|
|
echo "mysql command could not be found. Please install the MySQL client."
|
||
|
|
exit 1
|
||
|
|
fi
|
||
|
|
|
||
|
|
if [ -z "$DB_HOST" ] || [ -z "$DB_USER" ] || [ -z "$DB_PASS" ] || [ -z "$DB_NAME" ]; then
|
||
|
|
echo "Error: Missing required database environment variables."
|
||
|
|
echo "Please set DB_HOST, DB_USER, DB_PASS, and DB_NAME."
|
||
|
|
exit 1
|
||
|
|
fi
|
||
|
|
|
||
|
|
QUERY=$1
|
||
|
|
|
||
|
|
echo "Analyzing query: $QUERY"
|
||
|
|
|
||
|
|
EXPLAIN_OUTPUT=$(mysql -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "EXPLAIN $QUERY" --batch 2>/dev/null)
|
||
|
|
|
||
|
|
if [ $? -ne 0 ]; then
|
||
|
|
echo "Error executing EXPLAIN on the query. Please check your query and database credentials."
|
||
|
|
exit 1
|
||
|
|
fi
|
||
|
|
|
||
|
|
echo "--- EXPLAIN Output ---"
|
||
|
|
echo "$EXPLAIN_OUTPUT"
|
||
|
|
echo "----------------------"
|
||
|
|
|
||
|
|
SUGGESTIONS=""
|
||
|
|
|
||
|
|
# suggestion 1: Full table scan
|
||
|
|
if echo "$EXPLAIN_OUTPUT" | awk 'NR > 1' | awk '{print $5}' | grep -q "ALL"; then
|
||
|
|
TABLE=$(echo "$EXPLAIN_OUTPUT" | awk 'NR > 1 && $5 == "ALL" {print $3}')
|
||
|
|
SUGGESTIONS+=" - Consider adding an index to the join condition or WHERE clause for table '$TABLE' to avoid a full table scan.\n"
|
||
|
|
fi
|
||
|
|
|
||
|
|
# suggestion 2: Using filesort
|
||
|
|
if echo "$EXPLAIN_OUTPUT" | awk 'NR > 1' | awk '{print $10}' | grep -q "filesort"; then
|
||
|
|
SUGGESTIONS+=" - 'Using filesort' indicates an inefficient sort. Consider adding an index on the columns used in the ORDER BY clause.\n"
|
||
|
|
fi
|
||
|
|
|
||
|
|
# suggestion 3: Using temporary
|
||
|
|
if echo "$EXPLAIN_OUTPUT" | awk 'NR > 1' | awk '{print $10}' | grep -q "temporary"; then
|
||
|
|
SUGGESTIONS+=" - 'Using temporary' indicates the creation of a temporary table, which can be slow. This might be improved by adding an index.\n"
|
||
|
|
fi
|
||
|
|
|
||
|
|
|
||
|
|
if [ -z "$SUGGESTIONS" ]; then
|
||
|
|
echo "No obvious performance issues found."
|
||
|
|
else
|
||
|
|
echo "Actionable Suggestions:"
|
||
|
|
echo -e "$SUGGESTIONS"
|
||
|
|
fi
|