Hi Brent,
Using REGEXP did not really help with the performance. I need to do
whole word matching sowould prefer not to do LIKE '%Vice President%' as
it may return ome negative results.
I separated out some of the text based columns in to a different table
using MYISAM storage engine. Using FULLTEXT with IN BOOLEAN MODE was a
bit slow, but without the IN BOOLEAN MODE, it seems fast. However I
think the phrase searches are not working properly.
For example the below query returned records where the title was 'Vice
Chairman':
select emp.title from employment_title emp where MATCH(emp.title)
AGAINST('"Vice President"')
I have verified the syntax of phrase query, and it seems to bve correct.
Any idea why this is happening? Also if I have multiple phrases is the
following query syntax correct?
select emp.title from employment_title emp where MATCH(emp.title)
AGAINST('"V.P." "VP" "Vice President" "Vice-President"')
Thanks,
Harini
Brent Baisley wrote:
Egads! That's a lot of OR's. You probably want to use REGEXP instead
of all those OR's. REGEXP can be slow, but you'll be doing far less
comparisons than what you have in your current query so it may be
faster than what you have.
Something like this:
SELECT ...
WHERE ... emp.title REGEXP 'V[.]?P[.]?' OR emp.title REGEXP 'Vice[-]?
President'
I think that will match everything you have. At the very least you
don't have to check for periods, commas, space and everything else
before and after what you are searching on. Searching on emp.title
LIKE '%Vice President%', will find 'Vice President' anywhere in the
text, regardless of what come before or after it.
One thing you should think about trying to do is breaking out the
fields you need to do a full text search on into a separate table
that you can make MYISAM. You'll be adding a JOIN to your queries
that need to do the full text search, but it should be a lot quicker
and your queries much simpler. You'll then have a mix of InnoDB and
MYISAM tables, which is perfectly legal.
On Oct 4, 2005, at 11:17 AM, Harini Raghavan wrote:
Hi,
I am using MYSQL 4.1 database in my J2ee application. I am facing
performance issues with some queries that are being run on text
fields. Since MYISAM storage engine does not support transactions
(and my application requires the database tables to support
transaction), I have not been able to use FULL TEXT searches. I need
to perform token matching against the text fields and so use a lot
of LIKE expressions in the query. Below is one such query which is
taking almost 2 mins to execute.
select count(emp.id) from executive as exec1 , company comp,
target_company targetComp, employment as emp
where emp.executive_id = exec1.id
and emp.company_id = comp.id
and comp.id = targetComp.company_id and ((emp.title like 'V.P.' OR
emp.title like 'V.P. %' OR emp.title like 'V.P., %' OR emp.title
like 'V.P.. %' OR emp.title like '% V.P. %'
OR emp.title like '% V.P., %' OR emp.title like '% V.P.. %' OR
emp.title like '% V.P.' OR emp.title like '% V.P..')
OR (emp.title like 'VP' OR emp.title like 'VP %' OR emp.title like
'VP, %' OR emp.title like 'VP. %' OR emp.title like '% VP %'
OR emp.title like '% VP, %' OR emp.title like '% VP. %' OR emp.title
like '% VP' OR emp.title like '% VP.')
OR (emp.title like 'Vice President' OR emp.title like 'Vice
President %' OR emp.title like 'Vice President, %' OR emp.title like
'Vice President. %'
OR emp.title like '% Vice President %' OR emp.title like '% Vice
President, %' OR emp.title like '% Vice President. %'
OR emp.title like '% Vice President' OR emp.title like '% Vice
President.') OR (emp.title like 'Vice-President' OR emp.title like
'Vice-President %'
OR emp.title like 'Vice-President, %' OR emp.title like 'Vice-
President. %' OR emp.title like '% Vice-President %'
OR emp.title like '% Vice-President, %' OR emp.title like '% Vice-
President. %' OR emp.title like '% Vice-President'
OR emp.title like '% Vice-President.'))
and emp.active = 1
Does MYSQL provide any other option to perform text based searches?
Can someone suggest any tips for performance tuning the database in
this scenario?
Thanks,
Harini
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
