Rewriting query that is crashing my server

Forum Moderators: coopster

Message Too Old, No Replies

Rewriting query that is crashing my server

ntbgl

9:03 pm on Feb 7, 2009 (gmt 0)

My query is crashing my server because it is taking way too long. I desperitatly need to rewrite it to make it much for fast.

Original:
$query=mysql_query("SELECT * FROM news, publisher WHERE news.pub_hash=publisher.pubhash ORDER BY news.pubdate DESC LIMIT 40");

Where I'm at right now (Not working):

$query=mysql_query("SELECT * FROM publisher, localnews AS (SELECT * FROM news ORDER BY pubdate DESC LIMIT 40) WHERE localnews.pub_hash=publisher.pubhash ORDER BY localnews.pubdate DESC LIMIT 40");

I'm thinking my query was crashing the server because it had to first match up all of the publisher table with the news table, then select the 40 most recent. What I'm trying to accomplish in my rewrite is to get the 40 most recent files from the news table, then match only those entries with the publisher table.

Any help is very, very, very greatly appreciated!

STeeL

9:13 pm on Feb 7, 2009 (gmt 0)

Why not select 40 results from news table and then join it with publisher table?

ntbgl

9:37 pm on Feb 7, 2009 (gmt 0)

STeeL, that's what I'm trying to do with this rewrite, first grabbing 40 from the news table, calling it (probably incorrectly) as localnews, and trying to join just those 40 with the publisher table.

Might you know how to properly code that?

whoisgregg

11:02 pm on Feb 7, 2009 (gmt 0)

It just looks like the query is written incorrectly... When you do the subquery, you put "AS whatever" after the parentheses, not before it:

SELECT * FROM publisher, (SELECT * FROM news ORDER BY pubdate DESC LIMIT 40) AS localnews WHERE localnews.pub_hash=publisher.pubhash ORDER BY localnews.pubdate DESC LIMIT 40

Added: It can be really helpful to test your queries by running them through phpMyAdmin (or something similar) because those tools will often spit out descriptive errors.

ntbgl

3:00 am on Feb 8, 2009 (gmt 0)

That was exactly what I needed! Thank you for helping me cut the time from 120+ seconds to 1.2, and also for the great phpmyadmin tip. Thanks so much!