The EXPLAIN command can show why a certain query is slow, especially if the two options BUFFER and ANALYZE are used. There are some hints that enable us to decide whether the execution plan is good or not; these hints are as follows:
- The estimated row number in comparison with the actual rows: This is important because this parameter defines the method of the query's execution. There are two cases: the estimated number of rows may either be overestimated or underestimated. Wrong estimation affects the entire algorithm, which is used to fetch data from the hard disk, sort it, join it, and so on. In general, if the number of rows is overestimated, this affects performance, but not as much as if the number of rows is underestimated. On one hand, if one performs a nested loop join on very big tables, the execution time will increase exponentially assuming the nested loop join cost is O(n2) for simplicity. On the other hand, executing hash join on a small table...