1

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?

Community
  • 1
  • 1
Louis Loudog Trottier
  • 1,367
  • 13
  • 26
  • 1
    Why store the sorting in a table? Why not just sort when you pull the results? – AdamMc331 Jun 01 '15 at 17:28
  • 2
    Millions of rows are not that much for mysql and the query is pretty simple,but the issue I see is that you require consistent ranking,If you want to update the sort every time a row is deleted, with a trigger or something similar, than it will be slow. – Mihai Jun 01 '15 at 17:31
  • 1
    The best case scenario is that you have sufficient RAM for InnoDB to keep working data set there. If there's a single process modifying the sort column (for argument's sake let's assume you're doing it via Event Scheduler), then the whole operation will definitely complete in under a few tens of milliseconds, even if you had tens of millions of rows. – N.B. Jun 01 '15 at 17:58
  • @McAdam331 : So next time someone visit the page, the results are showed in the order that was saved. This was just an example but let say someone has a store or a list of links, and he want to display his items in a specific order. – Louis Loudog Trottier Jun 01 '15 at 18:24
  • As for the 2 other comments, like i said in my previous comment, this would ran every once in a while depending on the change made by an site administrator. Most likely triggered manually by some kind of 'save this ordering' button. i just don't like to when a site 'hangs' for 50 seconds because of a slow query running in the background. Thanks a bunch. – Louis Loudog Trottier Jun 01 '15 at 18:30

1 Answers1

1

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;
Rick James
  • 135,179
  • 13
  • 127
  • 222