Re: query optimization
Posted by:
Rick James
Date: March 13, 2010 09:11AM
Do you need the COUNT, or simply "there is at least one"?
It may not help a lot, but you could eliminate some of the stopwords thus:
# Need to declare stopword VARBINARY(...) -- this will allow the inequalities below to work correctly.
# Plus, have an index (probably PRIMARY KEY) on stopword.
# First use the regexps that start with the same letter as 'test':
SELECT 1 FROM stopwords
WHERE stopword LIKE CONCAT('^', LEFT('test', 1), '%')
AND 'test' REGEXP stopword
LIMIT 1;
# if you get 1 (as opposed to no rows) from that, exit with "got a stopword".
# else test harder:
SELECT 1 FROM stopwords
WHERE stopword < 'A'
AND 'test' REGEXP stopword
LIMIT 1;
# that will cover leading '.' '(' (etc)
# exit if get 1.
SELECT 1 FROM stopwords
WHERE stopword > 'Z' and stopword < 'a'
AND 'test' REGEXP stopword
LIMIT 1;
# that will cover leading '[' '\' (etc)
If you have a lot of stopwords beginning with .* this approach will need rethinking.
Subject
Views
Written By
Posted
2529
March 12, 2010 06:12AM
Re: query optimization
1598
March 13, 2010 09:11AM
Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.
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.