I have this update query:
explain UPDATE
qanda AS ans1
JOIN qanda AS ans2 ON ans2.related = ans1.related
JOIN qanda AS ques ON ans2.related = ques.id
SET ans1.acceptedanswer = IF( ans1.id <> 3, 0, IFNULL( ans1.acceptedanswer, 0 ) ^ b'1' ),
ans1.aadate = IF( ans1.id <> 3, ans1.aadate, 4353)
WHERE ques.author_id = 29
AND ans2.id = 3
AND ans2.author_id = 31
AND (ques.amount IS NULL or ans1.acceptedanswer IS NULL)
And here is the result of its EXPLAIN:
+------+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | ans2 | const | PRIMARY,author_id,related | PRIMARY | 4 | const | 1 | NULL |
| 2 | SIMPLE | ques | const | PRIMARY,author_id | PRIMARY | 4 | const | 1 | NULL |
| 3 | SIMPLE | ans1 | ALL | related | NULL | NULL | NULL | 4 | Using where |
+------+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------------+
It should be noted my table is just containing 4 rows of data.
Well is my structure fine? Or should I create such an index on such a column?
You have both questions and multiple answers in the same table? This seems 'wrong'. Suggest one table for questions, and one table for answers.
SHOW CREATE TABLE would be helpful.
Think "composite" indexes...
INDEX(author_id, id) (in that order) would probably be useful.
I suspect your JOIN to ans1 is incorrect (in a business-logic sense, not in an SQL-sense); check it.