1

i create a table lc178(emp_no int,salary int) from salaries (THAT salaries in mysql example database employees) for test rank score.

same sql ,different result after i created an index. i knew that index i created is the 'KILLER',but how does it work?

mysql> select salary, (select count(distinct salary) from lc178 where salary >= s.salary) rank from lc178 s order by s.salary desc;
+--------+------+ 
| salary | rank |
+--------+------+
|  46950 |    1 |
|  46950 |    1 |
|  44789 |    2 |
|  44789 |    2 |
|  44500 |    3 |
|  44500 |    3 |
|  44500 |    3 |
|  41797 |    4 |
|  40000 |    5 |
+--------+------+
9 rows in set (0.00 sec)
explain select salary, (select count(distinct salary) from lc178 where salary >= s.salary) rank from lc178 s order by s.salary desc;
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type        | table | type | possible_keys | key  | key_len |  ref  | rows | Extra          |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY            | s     | ALL  | NULL          | NULL | NULL    | NULL |    9 | Using filesort |
|  2 | DEPENDENT SUBQUERY | lc178 | ALL  | NULL          | NULL | NULL    | NULL |    9 | Using where    |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------+


mysql> create index salary_lc178 on lc178(salary);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select salary, (select count(distinct salary) from lc178 where salary >= s.salary) rank from lc178 s order by s.salary desc;
+--------+------+
| salary | rank |
+--------+------+
|  46950 |    1 |
|  46950 |    0 |
|  44789 |    0 |
|  44789 |    0 |
|  44500 |    0 |
|  44500 |    0 |
|  44500 |    0 |
|  41797 |    0 |
|  40000 |    0 |
+--------+------+
9 rows in set (0.00 sec)
mysql> explain select salary, (select count(distinct salary) from lc178 where salary >= s.salary) rank from lc178 s order by s.salary desc;
+----+--------------------+-------+-------+---------------+--------------+---------+------+------+--------------------------------------------------+
| id | select_type        | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                                            |
+----+--------------------+-------+-------+---------------+--------------+---------+------+------+--------------------------------------------------+
|  1 | PRIMARY            | s     | index | NULL          | salary_lc178 | 5       | NULL |    9 | Using index                                      |
|  2 | DEPENDENT SUBQUERY | lc178 | range | salary_lc178  | salary_lc178 | 5       | NULL |   10 | Using where; Using index for group-by (scanning) |
+----+--------------------+-------+-------+---------------+--------------+---------+------+------+--------------------------------------------------+

table declares and some sample data below:

CREATE TABLE `lc178` (`salary` int(11) DEFAULT NULL);
insert into lc178 values(40000);
insert into lc178 values(44500);
insert into lc178 values(41797);
insert into lc178 values(44500);
insert into lc178 values(44789);
insert into lc178 values(46950);
insert into lc178 values(46950);
insert into lc178 values(44500);
insert into lc178 values(44789);
ynwafuji
  • 11
  • 3
  • 1
    Possible duplicate of [ROW\_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) – Shadow Jul 07 '16 at 08:31
  • Can you post the table declares and some sample data to demonstrate the problem. – Kickstart Jul 07 '16 at 09:03
  • [rank scores](https://leetcode.com/problems/rank-scores/) , I'm working on this OJ – ynwafuji Jul 07 '16 at 09:17
  • I have just tried your table and values and it has worked fine for me, either with or without the index. I wouldn't expect the index to make any difference to the results – Kickstart Jul 07 '16 at 09:24
  • Just tested this on 5.7, 5.6 and 5.0. It works correctly. Please add your "explain" output. – Solarflare Jul 07 '16 at 09:24
  • @Solarflare,my version is 5.5.49-0ubuntu0.14.04.1.you can find "explain" output in my question – ynwafuji Jul 07 '16 at 09:48
  • There is no reason I can think of that the index if working correctly could cause this. All I can suggest is that you rebuild the index (ie _OPTIMIZE TABLE lc178_ ) – Kickstart Jul 07 '16 at 09:52
  • I recreate table with index ,then insert data. Still not correct.Is that a BUG of version 5.5? – ynwafuji Jul 07 '16 at 10:11
  • It would be a VERY major bug hence that seems unlikely. I have tried recreating it in 5.5 on SQL fiddle. However SQL Fiddle seems to be just failing. – Kickstart Jul 07 '16 at 10:33

0 Answers0