===== mysql> select # sql doesn't know my extreme protocol count(*) from (select max(ratingdate) as mrd, LEO_RoleRating.roleid, ratingtypeid from LEO_RoleRating join SelectedRoles on LEO_RoleRating.roleid = SelectedRoles.roleid where status>4 # gearing or better and loginname='d' group by roleid, ratingtypeid) sr1 join -- LEO_RoleRating -- weird problem related to transactions/locking?(*) LEO_RoleRating sr2 where sr1.roleid=sr2.roleid and sr1.ratingtypeid=sr2.ratingtypeid and mrd=sr2.ratingdate; mysql> mysql> -> -> -> -> -> -> -> -> -> \ -> -> +----------+ | count(*) | +----------+ | 2716 | +----------+ 1 row in set (31.96 sec) mysql> show profile; +----------------------+-----------+ | Status | Duration | +----------------------+-----------+ | (initialization) | 0.000003 | | Opening tables | 0.000037 | | System lock | 0.000009 | | Table lock | 0.000166 | | optimizing | 0.000037 | | statistics | 0.00027 | | preparing | 0.000048 | | Creating tmp table | 0.00004 | | executing | 0.000005 | | Copying to tmp table | 0.075587 | | Sorting result | 0.001306 | | Sending data | 0.003109 | | removing tmp table | 0.000028 | | Sending data | 0.000017 | | init | 0.000073 | | optimizing | 0.000034 | | statistics | 0.000068 | | preparing | 0.000038 | | executing | 0.000008 | | Sending data | 31.875867 | | end | 0.000026 | | query end | 0.00001 | | freeing items | 0.000041 | | closing tables | 0.000004 | | removing tmp table | 0.000034 | | closing tables | 0.000013 | | logging slow query | 0.005404 | +----------------------+-----------+ 27 rows in set (0.00 sec) mysql> explain select # sql doesn't know my extreme protocol count(*) from (select max(ratingdate) as mrd, LEO_RoleRating.roleid, ratingtypeid from LEO_RoleRating join SelectedRoles on LEO_RoleRating.roleid = SelectedRoles.roleid where status>4 # gearing or better and loginname='d' group by roleid, ratingtypeid) sr1 join -- LEO_RoleRating -- weird problem related to transactions/locking?(*) LEO_RoleRating sr2 where sr1.roleid=sr2.roleid and sr1.ratingtypeid=sr2.ratingtypeid and mrd=sr2.ratingdate; -> -> -> -> -> -> -> -> -> -> -> \ -> +----+-------------+----------------+------+------------------------+-----\ ----+---------+------------------------------------+------+--------------------\ --------------------------+ | id | select_type | table | type | possible_keys | key |\ key_len | ref | rows | Extra \ | +----+-------------+----------------+------+------------------------+---------+\ ---------+------------------------------------+------+-------------------------\ ---------------------+ | 1 | PRIMARY | | ALL | NULL | NULL |\ NULL | NULL | 2716 | \ | | 1 | PRIMARY | sr2 | ref | _when,_RoleID,_RTypeID | _when |\ 8 | sr1.mrd | 4 | Using where \ | | 2 | DERIVED | SelectedRoles | ref | lname | lname |\ 52 | | 4146 | Using where; Using tempo\ rary; Using filesort | | 2 | DERIVED | LEO_RoleRating | ref | _RoleID,Status | _RoleID |\ 5 | develop080401.SelectedRoles.RoleID | 17 | Using where \ | +----+-------------+----------------+------+------------------------+---------+\ ---------+------------------------------------+------+-------------------------\ ---------------------+ 4 rows in set (0.08 sec) ==== now I restart the server ==== ^Gmysql> show profile; No connection. Trying to reconnect... Connection id: 1 Current database: develop080401 Empty set (0.00 sec) mysql> SET profiling = 1; Query OK, 0 rows affected (0.00 sec) mysql> explain select # sql doesn't know my extreme protocol count(*) from (select max(ratingdate) as mrd, LEO_RoleRating.roleid, ratingtypeid from LEO_RoleRating join SelectedRoles on LEO_RoleRating.roleid = SelectedRoles.roleid where status>4 # gearing or better and loginname='d' group by roleid, ratingtypeid) sr1 join -- LEO_RoleRating -- weird problem related to transactions/locking?(*) LEO_RoleRating sr2 where sr1.roleid=sr2.roleid and sr1.ratingtypeid=sr2.ratingtypeid and mrd=sr2.ratingdate; -> -> -> -> -> -> -> -> -> -> -> \ -> +----+-------------+----------------+------+------------------------+-----\ ----+---------+------------------------------------+------+--------------------\ --------------------------+ | id | select_type | table | type | possible_keys | key |\ key_len | ref | rows | Extra \ | +----+-------------+----------------+------+------------------------+---------+\ ---------+------------------------------------+------+-------------------------\ ---------------------+ | 1 | PRIMARY | | ALL | NULL | NULL |\ NULL | NULL | 2716 | \ | | 1 | PRIMARY | sr2 | ref | _when,_RoleID,_RTypeID | _RoleID |\ 5 | sr1.roleid | 1 | Using where \ | | 2 | DERIVED | SelectedRoles | ref | lname | lname |\ 52 | | 4146 | Using where; Using tempo\ rary; Using filesort | | 2 | DERIVED | LEO_RoleRating | ref | _RoleID,Status | _RoleID |\ 5 | develop080401.SelectedRoles.RoleID | 1 | Using where \ | +----+-------------+----------------+------+------------------------+---------+\ ---------+------------------------------------+------+-------------------------\ ---------------------+ 4 rows in set (0.12 sec) mysql> select # sql doesn't know my extreme protocol count(*) from (select max(ratingdate) as mrd, LEO_RoleRating.roleid, ratingtypeid from LEO_RoleRating join SelectedRoles on LEO_RoleRating.roleid = SelectedRoles.roleid where status>4 # gearing or better and loginname='d' group by roleid, ratingtypeid) sr1 join -- LEO_RoleRating -- weird problem related to transactions/locking?(*) LEO_RoleRating sr2 where sr1.roleid=sr2.roleid and sr1.ratingtypeid=sr2.ratingtypeid and mrd=sr2.ratingdate; -> -> -> -> -> -> -> -> -> -> -> +-\ ---------+ | count(*) | +----------+ | 2716 | +----------+ 1 row in set (0.11 sec) mysql> show profile; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | (initialization) | 0.000242 | | Opening tables | 0.000031 | | System lock | 0.000007 | | Table lock | 0.000151 | | optimizing | 0.000034 | | statistics | 0.000232 | | preparing | 0.00004 | | Creating tmp table | 0.000029 | | executing | 0.000005 | | Copying to tmp table | 0.072015 | | Sorting result | 0.001215 | | Sending data | 0.003104 | | removing tmp table | 0.000053 | | Sending data | 0.000013 | | init | 0.00006 | | optimizing | 0.000026 | | statistics | 0.00006 | | preparing | 0.000033 | | executing | 0.000007 | | Sending data | 0.028958 | | end | 0.000014 | | query end | 0.000008 | | freeing items | 0.000028 | | closing tables | 0.000004 | | removing tmp table | 0.000033 | | closing tables | 0.000011 | | logging slow query | 0.000003 | +----------------------+----------+ 27 rows in set (0.00 sec) mysql>