Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql temporarily suppress unique index

I have a table with unique index on two columns, id_parent and sort_order to be precise

+----+-----------+------------+-------------+-------------+-------------+
| id | id_parent | sort_order | some_data   | other_data  | more_data   |
+----+-----------+------------+-------------+-------------+-------------+
| 1  |         1 |          1 | lorem ipsum | lorem ipsum | lorem ipsum |
| 2  |         1 |          2 | lorem ipsum | lorem ipsum | lorem ipsum |
| 3  |         1 |          3 | lorem ipsum | lorem ipsum | lorem ipsum |
+----+-----------+------------+-------------+-------------+-------------+

Now I want to update them, their data and their sort_order in one-go. sort_order would change from 1 - 2 - 3 to, for example 2 - 3 - 1.

But when I start running update statements, unique index block me, just as expected, saying that I can't have two rows with id_parent = 1 and sort_order = 2. Well, I could set it 4 for now, update other rows in correct order, and then set this one. But then, I would have to run an extra statement, and most probably add additional logic to my scripting language to determine correct order of updates. I also use ORM, and it becomes even more inconvinient.

My question now, is there some method to make mysql temporarily ignore this index? Like starting a special transaction, in which indexes would be calculated only right before commiting it?

like image 250
Nameless Avatar asked Dec 07 '11 05:12

Nameless


People also ask

How do I drop a unique key constraint in MySQL?

The syntax for dropping a unique constraint in MySQL is: ALTER TABLE table_name DROP INDEX constraint_name; table_name.

Can we disable index in MySQL?

Disable MySQL indexesIf you disable index updates, MySQL can import many rows with a single disk write; if you don't, MySQL will do many separate disk writes for each row. Disabling indexes makes the import go many, many times faster. It also minimizes the impact on other customers using the server.

Does unique index improve performance?

In addition to enforcing the uniqueness of data values, a unique index can also be used to improve data retrieval performance during query processing.


2 Answers

As far as I know that isn't possible.

The only time I've seen anything like that is that you can disable non unique keys on myisam tables. But not on InnoDB and not on unique keys.

However, to save you an update or two, there is no need to have the exact number 1, 2 and 3. You could as well have 4, 5 and 6. Right? You would use it in a order by and nothing else so the exact numbers aren't important. It will even save you an update if you're clever. From your example

update table set sort_order = 4 where sort_order = 1 and id = 1 and id_parent = 1;

New sort order is 2, 3, 1. And in just one update.

like image 84
Andreas Wederbrand Avatar answered Nov 15 '22 16:11

Andreas Wederbrand


‘But when I start running update statements…’ – I understand, you tried updating the values using multiple UPDATE statement, like in a loop. Is that so? How about updating them in one go? Like this, for example:

UPDATE atable
SET sort_order = CASE sort_order WHEN 3 THEN 1 ELSE sort_order + 1 END
WHERE id_parent = 1
  AND sort_order BETWEEN 1 AND 3

A single statement is atomic, so, by the time this update ends, the values of sort_order, although changed, remain unique.

I can't test this in MySQL, sorry, but it definitely works in SQL Server, and I believe the behaviour respects the standards.

like image 24
Andriy M Avatar answered Nov 15 '22 16:11

Andriy M