DB geekery

[nolly]

Does anyone have any ideas for speeding up these queries, especially the first one?

mysql> explain select clientID, count(t.testDigest) from tcsTestInfo t, tcsQuestion q, mcqdb m where q.testDigest=t.testDigest and q.questionID=m.ID and m.passage = '1034384208-txjt66' group by clientID;
+-------+--------+---------------+---------+---------+--------------+-------+----------------------------------------------+
| table | type   | possible_keys | key     | key_len | ref          | rows  | Extra                                        |
+-------+--------+---------------+---------+---------+--------------+-------+----------------------------------------------+
| q     | index  | PRIMARY       | PRIMARY |      80 | NULL         | 83652 | Using index; Using temporary; Using filesort |
| t     | eq_ref | PRIMARY       | PRIMARY |      40 | q.testDigest |     1 |                                              |
| m     | eq_ref | PRIMARY       | PRIMARY |      40 | q.questionID |     1 | where used                                   |
+-------+--------+---------------+---------+---------+--------------+-------+----------------------------------------------+
3 rows in set (0.00 sec)

mysql> explain select t.testDigest, testName, questionID from tcsTestInfo t, tcsQuestion q, mcqdb m where t.testDigest=q.testDigest and q.questionID=m.ID and m.passage = '1034384208-txjt66' and clientID = 'darkmatter';
+-------+--------+---------------+---------+---------+--------------+-------+-------------+
| table | type   | possible_keys | key     | key_len | ref          | rows  | Extra       |
+-------+--------+---------------+---------+---------+--------------+-------+-------------+
| q     | index  | PRIMARY       | PRIMARY |      80 | NULL         | 83652 | Using index |
| t     | eq_ref | PRIMARY       | PRIMARY |      40 | q.testDigest |     1 | where used  |
| m     | eq_ref | PRIMARY       | PRIMARY |      40 | q.questionID |     1 | where used  |
+-------+--------+---------------+---------+---------+--------------+-------+-------------+
3 rows in set (0.00 sec)

Comments

Hmmm...

[jhulten.livejournal.com]

Without a little more information on how the tablespace is constructed I do think I will be much help.