Re: NHibernate + MySQL = "IN Statement Issues"

[Darren Kopp]

No comment, though I am wondering if you have looked at full text search in sql server? If sql server doesn't work for you anymore, I would advise going w/ postgres over mysql.

What is the query that you are running? Likely you can avoid this by making a query that does exists statement vs an IN statement.

On Tuesday, September 25, 2012 3:28:18 AM UTC-6, Chris Cartlidge wrote:

Hi,

As you guys may know MySQL has an performance issue with "IN" statements (http://bugs.mysql.com/bug.php?id=9090). Currently we are using a MSSQL quite a few of our search queries use the "IN" statement to perform some sort of filtering and performance is fine.

However, we moved over to MySQL to take advantage of it's full text search and have become stuck with this issue. Has anyone had any experiences with the above and know of any solution?  

------------------------------------------------------------------------------------

This message and any attachments transmitted with it are confidential and intended solely for the use of the addressee(s). If you are not the addressee, you may not disseminate, distribute or copy this message and you should destroy it and kindly notify the sender by return. All opinions and conclusions in this message are solely those of the author and should be understood as neither given nor endorsed by Adgistics Limited unless specifically stated. Whilst we take reasonable precautions to ensure this message is free of viruses, opening and using this message is at the risk of the recipient.

Adgistics Limited: Registered in England and Wales, Company Number: 3859657, Registered Office 5 Copper Row, Tower Bridge Piazza, London SE1 2LH. Vat Registered Number: 752019256.

[Tyler Burd]

You could try externalizing the full text searches via Lucene.NET or NHibernate.Search (which uses Lucene.NET internally).  I don’t have much experience with either, but I believe the source for NHibernate.Search is located at https://nhcontrib.svn.sourceforge.net/svnroot/nhcontrib/trunk/src/NHibernate.Search/  and it should be available on NuGet.

 

This would allow you to use whichever database you like (and personally I would NEVER choose MySQL over SQL Server).

 

-Tyler Burd

 

From: nhu...@googlegroups.com [mailto:nhu...@googlegroups.com] On Behalf Of Chris Cartlidge
Sent: Tuesday, September 25, 2012 10:21 AM
To: nhu...@googlegroups.com
Subject: [nhusers] Re: NHibernate + MySQL = "IN Statement Issues"

 

This is true, however to retro fit this into our existing code base would be wise however it's that large that it's not really feasible. 

 

SQL Server does currently work for us at the moment it's we are trying to evaluate which has the better full text searching capabilities. However when doing so we ran into said issue.

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/DPx7a-JcDSgJ.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.