Re: Complex multiple command query

[Rick James]

Another thing to consider is using AUTO_INCREMENT as a counter, instead of @num variables. It will probably run much faster.

To do so, you need to CREATE TEMPORARY TABLE ... SELECT... And you may need to apply the INDEX after creating that table.

Also look at the compound key trick in MyISAM (not InnoDB) that lets you have
PRIMARY KEY (foo, row_number)
where row_number is AUTO_INCREMENT and starts from 1 for each different 'foo'. This would achieve your "x.row_number <= 2".

There is rarely a real need to send multiple statements at the same time. Your "session" maintains temporary tables and @num values.