HereToFigureThisOut HereToFigureThisOut -4 years ago 75
SQL Question

Anyway to improve this MySQL database query

I'm looking for anyway to improve this query. Basically, I've got a table named

tool_categories
. This table contains only two columns:
categoryID
which is a primary key and unique; and then
categoryName
which is also unique. The query below serves the purpose of editing the order of these categories from an admin page form. I call
SELECT * FROM tool_categories ORDER BY categoryID
to sort these properly into a
<select>
drop down. I know there has to be a way to optimize the query below, perhaps getting rid of a few of those lines completely?

UPDATE tool_categories SET categoryID = 0 WHERE categoryID = ?
UPDATE tool_categories SET categoryID = categoryID - 1 WHERE categoryID > ?
UPDATE tools SET categoryID = categoryID - 1 WHERE categoryID > ?
ALTER TABLE tool_categories DROP INDEX categoryID
ALTER TABLE tool_categories DROP PRIMARY KEY
UPDATE tool_categories SET categoryID = categoryID + 1 WHERE categoryID >= ?
UPDATE tools SET categoryID = categoryID + 1 WHERE categoryID >= ?
ALTER TABLE tool_categories ADD INDEX categoryID (categoryID)
ALTER TABLE tool_categories ADD PRIMARY KEY (categoryID)
UPDATE tool_categories SET categoryID = ? , categoryName = ? WHERE categoryID = 0

Answer Source

Yeah, don't use a primary key for order. I would add a new column, display_order which can be an int.

Then you don't have to remove & re-add the index and the primary key. You also then don't have to update the other table as well, as the join key won't change with the order.

Then, moving a tool_category to the top of the list can be done like so:

# move everything below where the item was up to close the gap
update tool_categories set display_order = display_order - 1 where display_order > <old position>;

# move everything down to make room at the top
update tool_categories set display_order = display_order + 1;

# move the specific item to the top
update tool_categories set display_order = 0 where category_id = ?;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download