DIRACGrid/DIRAC — pull request #869 by graciani (2012-07-31 09:22:06+00:00)
Title: FIX: select those fields that are used reduces the size of tmptable

This reduces the time of query execution by a factor 10 for a DB with > 150.000 entries:

mysql> SELECT count(*) FROM SubRequests AS A, ( SELECT RequestID, ExecutionOrder FROM ( SELECT * from ( SELECT * from SubRequests WHERE Status IN ( 'TmpWaiting', 'Assigned', 'Waiting' ) ORDER BY ExecutionOrder, LastUpdate ) as T1 GROUP BY RequestID ) as T2 WHERE RequestType = 'diset' ORDER BY LastUpdate limit 100 ) AS B WHERE A.RequestID = B.RequestID AND A.ExecutionOrder = B.ExecutionOrder AND A.Status = 'TmpWaiting' AND A.RequestType = 'diset';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (31.94 sec)

mysql> SELECT count(*) FROM SubRequests AS A, ( SELECT RequestID, ExecutionOrder FROM ( SELECT * from ( SELECT RequestID,ExecutionOrder,Status,RequestType, LastUpdate from SubRequests WHERE Status IN ( 'TmpWaiting', 'Assigned', 'Waiting' ) ORDER BY ExecutionOrder, LastUpdate ) as T1 GROUP BY RequestID ) as T2 WHERE RequestType = 'diset' ORDER BY LastUpdate limit 100 ) AS B WHERE A.RequestID = B.RequestID AND A.ExecutionOrder = B.ExecutionOrder AND A.Status = 'TmpWaiting' AND A.RequestType = 'diset';
+----------+
| count(*) |
+----------+
| 13 |
+----------+
1 row in set (3.41 sec)

addresses #694