PHP - mysql query execution time | reduce query
العربية
български
català
中文
čeština
dansk
Nederlands
eesti
suomi
français
Deutsch
Ελληνικά
עברית
हिंदी
magyar
Bahasa Indonesia
italiano
日本語
한국어
latviešu
lietuvių
norsk
polski
Português
română
русский
slovenčina
slovenski
español
svenska
ไทย
Türkçe
українська
Tiếng Việt
I have a query which let me to change users order.
here is my query:
update test set orderID =
case orderID
when (select orderID from ( select * from test where orderID > ( select orderID from test where id = 'id I want to move up' ) limit 1) as nextOrderID ) then ( select orderID from ( select * from test where id = 'id I want to move up') as nextOrderID )
when ( select orderID from ( select * from test where id = 'id I want to move up' ) as nextOrderID ) then (select orderID from ( select * from test where orderID > ( select orderID from test where id = 'id I want to move up' ) limit 1) as nextOrderID )
else
orderID
end
I want to know if it won't took a lot of time to execute that query, and if it will , how to make it load faster, and is it possible to reduce that query?
Update:
i edited the code a bit so now i think it will execute query faster.. here is a part of code :
$query = "
SELECT (
SELECT orderID
FROM test WHERE id = 'user id that i want to move up'
) AS user_order,
(
SELECT orderID
FROM test WHERE orderID > user_order
ORDER BY orderID
LIMIT 0,1
) AS nextUser_order
";
$result = mysql_query($query);
$data = mysql_fetch_assoc($result);
$query = "
UPDATE test SET orderID = IF(orderID='{$data[nextUser_order]}',
'{$data[user_order]}', '{$data[nextUser_order]}')
WHERE orderID IN ('{$data[nextUser_order]}', '{$data[user_order]}');
";
$result = mysql_query($query);
Answer |
I want to know if it won't took a lot of time to execute that query,
er...do you mean you want to know if the query will take a lot of time to execute? Certainly more than it needs to - but a lot depends on the structure of your database, the data within it, and your definition of 'a lot of time'.
The query is a horrendous mess. Leaving aside the unnecessary complexity of the query, it is also wrong - you are using LIMIT to retrieve the next entry in a list without defining how that list should be sorted.
It looks like it is supposed to change the ordering of a list. It'd be far more sensible to (this assumes orderID is unique):
DELIMITER $$
CREATE PROCEDURE bubble_up(IN p_selected INTEGER)
BEGIN
DECLARE l_selected_seq INTEGER;
DECLARE l_replaced_seq INTEGER;
SELECT orderID into l_selected_seq
FROM test
WHERE id=p_selected;
IF (l_selected>1) THEN
SELECT orderID INTO l_replaced_seq
WHERE orderID>l_selected_seq
ORDER BY orderID
LIMIT 0,1;
-- swap the values
UPDATE test SET orderID = IF(orderID=l_replaced_seq,
l_selected_seq, l_replaced_seq)
WHERE orderID IN (l_replaced_seq, l_selected_seq);
END IF
END$$