Can This Be Optimized?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
supermike
Forum Contributor
Posts: 193
Joined: Tue Feb 28, 2006 8:30 pm
Location: Somewhere in the Desert, USA

Can This Be Optimized?

Post by supermike »

Imagine a set of classified listings that one can pin on a home page of a site. Eventually the listings are in need to be deleted, and thus the pin needs to be deleted. I have a query for this, but I believe over time, when the classified listings table (listed_q) becomes massive, this query will start to take a very long time until PHP times out. Do you think you might have a way to optimize this?

Code: Select all

DELETE FROM
    pins
WHERE
    fkey_listing NOT IN (
        SELECT
            pkey
        FROM
            listed_q
    );
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Can This Be Optimized?

Post by onion2k »

Why aren't you deleting the record(s) in `pins` when the listing record in `listed_q` is deleted? At some point you must be doing something like ... DELETE FROM `listed_q` WHERE `pkey` = 123 ... just do a second query at the same point ... DELETE FROM `pins` WHERE `fkey_listing` = 123. If you did that you'd never have any records in `pins` that refer to deleted records in `listing_q`, and this problem would never arise.

On a side note, calling your primary key `pkey` isn't something I'd do. It makes more sense to call it something meaningful like `listing_id` ... that way when you're joining lots of tables together it's a lot easier to read the code.
supermike
Forum Contributor
Posts: 193
Joined: Tue Feb 28, 2006 8:30 pm
Location: Somewhere in the Desert, USA

Re: Can This Be Optimized?

Post by supermike »

onion2k wrote:Why aren't you deleting the record(s) in `pins` when the listing record in `listed_q` is deleted?
Ah, this is a failsafe. I do have that other delete query written.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Can This Be Optimized?

Post by onion2k »

If it's a failsafe then presumably it's only going to be used occasionally, and it can be run at specific times when traffic is minimal, right? I wouldn't bother spending time optimising it.
Post Reply