mysql变量使用索引,使用变量在存在索引的情况下MySQL排名

Using the classic trick of using @N=@N + 1 to get the rank of items on some ordered column. Now before ordering I need to filter out some values from the base table by inner joining it with some other table. So the query looks like this -:

SET @N=0;

SELECT

@N := @N + 1 AS rank,

fa.id,

fa.val

FROM

table1 AS fa

INNER JOIN table2 AS em

ON em.id = fa.id

AND em.type = "A"

ORDER BY fa.val ;

The issue is if I don't have an index on the em.type, then everything works fine but if I put an index on em.type then hell unleashes and the rank values instead of coming ordered by the val column comes in the order the rows are stored in the em table.

here are sample outputs -:

without index-:

rank id val

1 05F8C7 55050.000000

2 05HJDG 51404.733458

3 05TK1Z 46972.008208

4 05F2TR 46900.000000

5 05F349 44433.412847

6 06C2BT 43750.000000

7 0012X3 42000.000000

8 05MMPK 39430.399658

9 05MLW5 39054.046383

10 062D20 35550.000000

with index-:

rank id val

480 05F8C7 55050.000000

629 05HJDG 51404.733458

1603 05TK1Z 46972.008208

466 05F2TR 46900.000000

467 05F349 44433.412847

3534 06C2BT 43750.000000

15 0012X3 42000.000000

1109 05MMPK 39430.399658

1087 05MLW5 39054.046383

2544 062D20 35550.000000

I believe the use of indexes should be completely transparent and outputs should not be effected by it. Is this a bug in MySQL?

解决方案

This "trick" was a bomb waiting to explode. A clever optimizer will evaluate a query as it sees fits, optimizing for speed - that's why it's called optimizer. I don't think this use of MySQL variables was documented to work as you expect it to work, but it was working.

Was working, up until recent improvements on the MariaDB optimizer. It will probably break as well in the mainstream MySQL as there are several improvements on the optimizer in the (yet to be released, still beta) 5.6 version.

What you can do (until MySQL implemented window functions) is to use a self-join and a grouping. Results will be consistent, no matter what future improvements are done in the optimizer. Downside is that that it may not be very efficient:

SELECT

COUNT(*) AS rank,

fa.id,

fa.val

FROM

table1 AS fa

INNER JOIN table2 AS em

ON em.id = fa.id

AND em.type = 'A'

INNER JOIN

table1 AS fa2

INNER JOIN table2 AS em2

ON em2.id = fa2.id

AND em2.type = 'A'

ON fa2.id <= fa.id

--- assuming that `id` is the Primary Key of the table

GROUP BY fa.id

ORDER BY fa.val ;