Emulating Analytic (AKA Ranking) Functions with MySQL
Subject:   MySQL version
Date:   2007-04-06 10:16:23
From:   sfaroult
Response to: MySQL version


Where did I say I was using InnoDB? I didn't want to compare Oracle to Oracle ;-). Actually, as my point was a purely SQL point, I haven't specified any storage engine - which means that my table is of the default MyISAM type. That said, the rewriting you suggest is indeed interesting. I have used the code of your second rewriting, added the missing condition on RANK and ran it twice, one without the index, and one with the index. There is a noted improvement WITHOUT the index on the query with the subquery in the SELECT list, as could indeed be expected:
30 rows in set (1 min 48.16 sec)

With the index, though, it's quite comparable:
30 rows in set (33.57 sec)

The problem with the index is the way I have generated my data ( ... randomly). As a result, employees from a same department are spread all over the table. We might expect greater efficiency if employees were physically clustered by DEPTNO (by "clustered" I mean of course no more than "if the rows were close from each other").