CODEWITHSUNDEEP
mysqlperformance
Louis Loudog Trottier
Louis Loudog Trottier

Reputation: 1377

Mysql Incrementing variable performance

I has looking for a way to sort items in a mysql table. Here a simplified version of the table

sqlfiddle => http://sqlfiddle.com/#!2/78521b/3/0

CREATE TABLE IF NOT EXISTS `test_sort` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sort` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sort` (`sort`)
);
INSERT INTO `test_sort` (`id`,`sort`, `name`) VALUES
(1, 1, 'Joker'),
(2, 3, 'Queen of Spade'),
(3, 6, 'King of Heart'),
(4, 4, 'Ace of Diamond'),
(5, 17, 'Three of Clubs'),
(6, 60, 'Seven of Clubs'),
(7, 2, 'Ten of Spades'),
(8, 5, 'Ace of Heart');

So once the items (cards) has been sorted by the user i want to run the query on the sort column so it remains consistent.

Solution found here : MySQL update a field with an incrementing variable

SET @n=0;
UPDATE `test_sort` SET `sort` = @n := @n + 1 ORDER BY `sort`

QUESTION: how this query would act (performance wise) if it was used on thousands (or millions) of records?

Upvotes: 1

Views: 388

Answers (1)

Rick James
Rick James

Reputation: 142366

Don't store sort in the table; store it in a separate table. Furthermore, don't UPDATE that table, recreate it. Further-furthermore, use the following to avoid any downtime:

CREATE TABLE New SELECT ... -- generate new sort order
RENAME TABLE Sort TO Old, New To Sort;
DROP TABLE Old;

Upvotes: 1

Related Questions