MySQL Forums
Forum List  »  Optimizer & Parser

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.

Options: ReplyQuote


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.