MySQL Forums
Forum List  »  Performance

Re: Demanding Query
Posted by: Tobin Harris
Date: July 24, 2005 05:35AM

Hi Jay,

Thank you very much for your thoughts, you're right in that I'm checking for exsistance.

This query is a duplication check. I'm checking on the first 900 characters of the body because the business rules dictate that an article is a duplicate if it's body text matches that of an existing article - the first 900 characters is considered a good enough match.

The query is determines if an article should be inserted, and is execute by the application (0-3 times per second). I would have done implemented this logic using a unique key, but the unique rules vary depending from web site to web site (for example, some websites use the same url to publish new articles each week, so url isn't always present of this dupe check query).

Thanks for pointing out the UNION trick, that's great cause I can use both indexes. Is there a way of not making the 2nd select clause cheaper if the first returns a value. I tried something like this but no luck...

SELECT @found := 1, 1 FROM articles
WHERE url_hash = md5(?url)
LIMIT 1
UNION
SELECT 1,1 FROM articles
WHERE NOT @found = 1
AND headline_hash = md5(?headline)
LIMIT 1;

I was hoping the "WHERE NOT @found=1" would cause MySql to abandon the second part of the query if the first part already returned a value. Or does the limit 1 make this happen automatically?

To make the body lookup cheaper, I considered adding a hash field for the first 900 characters, which might be enough?

I forgot to mention that I'm using the InnoDB engine for this table (so no .myd file). I chose that because the table is subject to many lookups and inserts simaltaneously, and I understand that InnoDBs granular locking is more suited to such an environment. I may be wrong though!

Again, thanks for your help, any thoughts welcome.

Tobin

Options: ReplyQuote


Subject
Views
Written By
Posted
2513
July 23, 2005 11:31AM
1781
July 23, 2005 01:52PM
Re: Demanding Query
1684
July 24, 2005 05:35AM
1649
July 24, 2005 09:39AM
1715
July 24, 2005 11:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.