Why mysql query with varchar variable performs extremely slow?

Question

I have the following mysql table:

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `login` varchar(64) NOT NULL,
  `state` enum("state_1","state_2","state_3") NOT NULL
  PRIMARY KEY (`id`),
  KEY `ix_date` (`date`),
  KEY `ix_login_date` (`login`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

This query performs extremely slow (more than 10 minutes):

SELECT
  date,
  @prev_login AS p_login,
  (@prev_login := my_table.login) AS login,
  @prev_state AS from_state,
  (@prev_state := my_table.state) AS state
FROM my_table
JOIN (SELECT @prev_login := NULL) pl
JOIN (SELECT @prev_state := NULL) ps
ORDER BY login, date;

But after removing (@prev_login := my_table.login) AS login string the query finishes in less than a second:

SELECT
  date,
  @prev_login AS p_login,
  @prev_state AS from_state,
  (@prev_state := my_table.state) AS state
FROM my_table
JOIN (SELECT @prev_login := NULL) pl
JOIN (SELECT @prev_state := NULL) ps
ORDER BY login, date;

EXPLAIN outputs the same result for both cases. Why this can happen and how to speed up the first query?

UPD. I've found questions (1, 2) where such slowdown is the result of inappropriate variable collation, but I see no ways it can be applied to my case.

@the_pete, it was an error in the question. There should be `my_table`. Fixed. — citxx, Apr 17 '14 at 14:02
Maybe you should consider indexing your 32M records table on the login column. — StephaneM, Apr 17 '14 at 14:19
@StephaneM, I see no reasons for the query to use such index. Both queries use only primary key. There is no index for `state`, but the same computations for that column are fast. The only difference is the type. — citxx, Apr 17 '14 at 14:54
The only thing that I'm unclear about is the join in the query... what happens if you removee the join ? Why do you need a "Join" and not "Where"? — evenro, Apr 17 '14 at 15:52
@evenro, the [variant without `JOIN`](http://pastebin.com/CdJiAYBJ) shows the same result. — citxx, Apr 18 '14 at 09:15

score 0 · Accepted Answer · answered Apr 18 '14 at 21:41

I've just found the solution. The reason of slow execution is the sorting by login field. Selecting of (@prev_login := my_table.login) AS login field replaces the original login field, so the index can't be used for ordering. The query should be rewritten in this way:

SELECT
  date,
  @prev_login AS p_login,
  (@prev_login := my_table.login) AS tmp_login,
  login,
  @prev_state AS from_state,
  (@prev_state := my_table.state) AS state
FROM my_table
JOIN (SELECT @prev_login := NULL) pl
JOIN (SELECT @prev_state := NULL) ps
ORDER BY login, date;

P.S. I still don't understand why EXPLAIN doesn't show this issue.

Why mysql query with varchar variable performs extremely slow?

1 Answers1