mysql query execution time | reduce query

Question

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);

This question belongs to http://codereview.stackexchange.com — Mithun Sreedharan, Jun 17 '11 at 10:09
Also.. without telling us the actions you'd like to invoke (and how your DB scheme looks) we can't help you really. — Gerben Jacobs, Jun 17 '11 at 10:12
The very least you could do is replace the `select *` by `select orderID`. — Jacco, Jun 17 '11 at 10:23
Aside from being a very ugly update query with almost no sensible purpose... what are you REALLY trying to do. — DRapp, Jun 17 '11 at 10:49
@DRapp, it's just an simple example, and i said " to change users order " just to let you to understand, but in real case i't won't change users order it will change items order, it complex scheme.. and i can't explain right in english — John, Jun 17 '11 at 11:37

score 2 · Accepted Answer · answered Jun 17 '11 at 11:29

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$$

score 0 · Answer 2 · answered Jun 17 '11 at 10:16

0

Isn't a bit too much logic for a query ? I'd use some code to achieve the result ...

If there's some reason for being forced to a complex mysql query can you post the desc of the table ? First thing I would try an explain of the selects in order to assure I have the necessary indexes ...

answered Jun 17 '11 at 10:16

simone cittadini

358
2
14

i updated my question, and i used some code to acheive the result, can you see if it is better now? – John Jun 17 '11 at 18:50

Karolis · Answer 3 · 2011-06-17T11:00:02.037

0

I see some issues, but I think the biggest one is that you are updating all the table while I see that you actually need to update particular row(s) only. You should add WHERE clause in your UPDATE statement to prevent the rows from locking (and updating) which you do not need to update.

edited Jun 17 '11 at 11:00

answered Jun 17 '11 at 10:40

Karolis

9,396
29
38

mysql query execution time | reduce query

3 Answers3