MySQL Fun

Posted on May 4th, 2007 by Greg Allard in MySQL | Comments

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. I can’t use group by on an update and if I can’t use group by then I can’t use the MAX function. So it will basically update the records multiple times and won’t get the highest value like I need it to.

I use webfaction to host a lot of my django projects. It has an easy setup that will get you developing quickly and a great community of talented programmers. There is also a quick setup for rails, wordpress, and a lot more.

Tips for MySQL to PostgreSQL Switch If you’ve decided to move a few tables from MySQL to PostgreSQL, these few tips might help. I won’t get...
How To Use Triggers to Track Changes in MySQL Setting constraints and rules in the database is better than writing special code to handle the same task since it...
How to Break a MySQL Left Join Care must be taken when placing conditions on the results of the right-hand table of a LEFT JOIN because it...

Greg Allard

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

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

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

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!

blog comments powered by Disqus

About the Author

Greg Allard is the programmer behind IvyLees The network for PR and business and Presskit'n Do-It-Yourself Public Relations.

He is also one of the developers in Match Strike: we build websites.

More about Greg Allard »

Find him on:
Recent Posts
Recent Comments
Tags
AJAX Apache Authorization AWS CURL CyTE Data Access Data Types Debian Django Forum Framework GMP GPS How To iPhone JavaScript JSP LDAP Mod Rewrite mod_python MorfU MySQL OAuth OpenID Open Source php phpMyAdmin Pinax PostgreSQL Python Rails regex Rickety Van Ruby Security Server Administration SSO Startup Weekend TinyID Trigger Twitter Ubuntu Usability Wiki

Code Spatter

MySQL Fun

Related posts:

About the Author

Recent Posts

Recent Comments

Tags