For the query above, the following recommendations will be helpful as part of the SQL tuning process. You'll find 3 sections below:
Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
Use Numeric Column Types For Numeric Values (query line: 26): Referencing a numeric value (e.g. 4) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
The optimized query:
subject.title AS subject_title,
region.title AS region_title,
topic.title AS topic_title
question.subject_id = subject.id
question.region_id = region.id
question.topic_id = topic.id
question.subject_id = '4'
rand1 DESC LIMIT 20