Page 1 of 1
Can This Be Optimized?
Posted: Fri Mar 21, 2008 11:46 pm
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
);
Re: Can This Be Optimized?
Posted: Sat Mar 22, 2008 4:40 am
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.
Re: Can This Be Optimized?
Posted: Sat Mar 22, 2008 5:53 pm
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.
Re: Can This Be Optimized?
Posted: Sun Mar 23, 2008 5:02 am
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.