If you are using join in MYSQL for adding multiple tables and you didn't configure server engine and storage architecture, then your query may take a minute in execution. The default time of MYSQL query execution is 0.5 second. The execution time of MySQL query depends on various factors like type of server engine, storage architecture, database and table structure, load and contention, and network latency.
Here we are going to see some basic things that increase MySQL query execution time.
- Avoid functions in where clauses
- Avoid arithmetic in where clauses
- Avoid "Outer JOIN"
- Avoid " GROUP BY, ORDER BY, LIKE, DISTINCT " operator. They are consuming more time.
- Do not use sub-queries
For reducing MySQL query execution time see below steps
- Creating better indexes
- Use "explain"
- Join all table with each other by unique and same column name
- Use short query with supported column
- Use best hardware configuration with the hosting server
- Increase cache and RAM in your hardware.
How to check query performance in mysql
//Record start time before query is executed
$start_time = microtime(true);
//Run your MYSQL query
mysqli_query($db, "SELECT * FROM table_name ");
//After query has finished running, Record the end time
$end _time= microtime(true);
//Calculate the difference of start time and end time of mysql query execution in microseconds
$diff = $end_time - $start_time;
//Format the time so that it only shows 10 decimal places
$ActualqueryTime = number_format($diff, 10);
//Print out the seconds it took for the query to execute
echo "MYSQL query took $ ActualqueryTime seconds.";