DISQUS

Code Spatter: MySQL Fun

  • 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
  • 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.
  • 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)
  • 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!