1 SIMPLE c index PRIMARY PRIMARY 10 NULL 192137 Using index; Using temporary; Using filesort 1 SIMPLE a eq_ref PRIMARY,all PRIMARY 4 base.c.article_id 1
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 192137 2 DERIVED c index PRIMARY PRIMARY 10 NULL 192137 Using index; Using temporary; Using filesort 2 DERIVED a eq_ref PRIMARY,all PRIMARY 4 base.c.article_id 1
SET @cat:='', @num:=1;
UPDATE `category`
SET `sort`=max(@num := IF( @cat = `category_id`, @num +1, 1 ), (@cat:=`category_id`)-999999) order by `category_id`, `sort_temp` ;