Hello.
> Does MYSQL provide any other option to perform text based searches? Can > someone suggest any tips for performance tuning the database in this > scenario? > Use the same queries linked with UNION instead of a lot of ORs in WHERE clause. For example this query can't use index (at least in 4.1 branch): SELECT A FROM B WHERE A LIKE 'h%' OR A LIKE 'l%'; But this one which is equal can: SELECT A FROM B WHERE A LIKE 'h%' UNION SELECT A FROM B WHERE A LIKE 'l%'; Check that you have an index on emp.title. MySQL 5.0 has so known 'Index Merge Optimization' which might be helpful in your case. See: http://dev.mysql.com/doc/mysql/en/index-merge-optimization.html 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 > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
