DISQUS

DISQUS Hello! Code Spatter is using DISQUS, a powerful comment system, to manage its comments. Learn more.

Community Page

Code Spatter

Jump to original thread »
Author

MySQL Fun

Started by Greg Allard · 10 months ago

Trying to find a way to update a bunch of records in the request table by changing request_taken_time to a value in another table. The problem is, there are multiple records in the history table so there will be duplicate queries and it may not get the most recent time which is what is wanted. [ ... Continue reading »

4 comments

  • Greg Allard

    1 year ago

    I think I might need to do some nested query where I set request_taken_time = (SELECT history_created order by blah blah) That may be the only way to get it to work accurately, however it will be slow since it doesn't seem to do nested queries efficiently
    reply
  • Greg Allard

    1 year ago

    I'm doing this

    UPDATE tbd_requests
    SET request_taken_time =
    (
    SELECT history_created
    FROM tbd_history
    WHERE tbd_history.request_id = tbd_requests.request_id
    AND history_action LIKE 'taken'
    ORDER BY history_created DESC
    LIMIT 0, 1
    )
    WHERE request_type != 11
    ORDER BY request_id ASC
    LIMIT 0, 1

    and getting this

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1' at line 13

    Fate doesn't want to allow me to restructure this database, it wants me to forever have slow queries.
    reply
  • Greg Allard

    1 year ago

    Left the limit out even though I thought it wouldn't be able to do it, but it went through.

    Affected rows: 4176 (Query took 176.0788 sec)
    reply
  • Greg Allard

    1 year ago

    After all the fighting to get this to a better table schema, it increased the query I was trying to optimize from 23.0878 seconds to 0.0638 seconds. That's like 36,000% better!
    reply

Add New Comment

Returning? Login