Re: Fulltext index -first query slow, subsequent queries fast
Hi Is sphinxsearch avialable only on for windows regards anandkl On 6/13/08, Rory McKinley [EMAIL PROTECTED] wrote: mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike snip I have read about sphinx and the good performance boost it provides - unfortunately there is a lot of legacy code reading off the db, so I will need to get all sorts of stuff signed off, before I can make any major changes ;). But I will definitely look into it so that I can offer it as a possible solution. Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
-Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 10:48 AM To: [EMAIL PROTECTED] Cc: mos; mysql@lists.mysql.com Subject: Re: Fulltext index -first query slow, subsequent queries fast Hi Is sphinxsearch avialable only on for windows regards anandkl On 6/13/08, Rory McKinley [EMAIL PROTECTED] wrote: mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ ---8--- snip GIYF: http://www.linux.com/feature/118721 I believe the *binaries* are only *pre-compiled* for Windows. Search the Sphinx site for info about compilation, or crack open one of the .tgz (an immediate I'm probably a *nix package flag) releases. I'll bet you can compile it yourself on *nix. (The article I've linked to also talks about using Sphinx in BSD distros.) Hope that helps, Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
-Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 3:20 PM To: mysql Subject: Fulltext index -first query slow, subsequent queries fast Hi List I have a table with a fulltext index across five fields, with about 2.2 million records and a data size of about 5.6 GB (index another 3.5 GB). When I test a query that uses fulltext matching, the first run takes about 15-16 seconds to complete. The second run takes about 0.1 sec and subsequent runs will all all ge around the 0.1 sec time. The query that I use has a call to NOW() as one of the criteria, so i know that the query results will not be cached. Yet, the times would suggest that some sort of caching effect is being observed. [JS] It may well be the file system that is doing the caching. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
Jerry Schwartz wrote: File system, or disk caching, uses some kind of algorithm to hold chunks of files in system RAM. That way a program can get to it more quickly than if it had to go out to the disk. The algorithms vary, depending upon the smarts of the program and the smarts of the file system. The system might keep the most recently used stuff, the most frequently used stuff, even the stuff it thinks you will need based upon the pattern of use. Regards, snip Hi Jerry Thanks for the explanation. So, in short, I am most likely hitting a wall with the fulltext index, and I am just getting lucky cos of the disk caching, but I am not going to be able to get away from that initial slow load...rats. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 4:57 PM To: Jerry Schwartz; mysql Subject: Re: Fulltext index -first query slow, subsequent queries fast Jerry Schwartz wrote: File system, or disk caching, uses some kind of algorithm to hold chunks of files in system RAM. That way a program can get to it more quickly than if it had to go out to the disk. The algorithms vary, depending upon the smarts of the program and the smarts of the file system. The system might keep the most recently used stuff, the most frequently used stuff, even the stuff it thinks you will need based upon the pattern of use. Regards, snip Hi Jerry Thanks for the explanation. So, in short, I am most likely hitting a wall with the fulltext index, and I am just getting lucky cos of the disk caching, but I am not going to be able to get away from that initial slow load...rats. [JS] I can't agree or disagree. I used to be a performance consultant, but that was in another life and I don't know anything about MySQL's own caching algorithms. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
At 02:20 PM 6/12/2008, you wrote: Hi List I have a table with a fulltext index across five fields, with about 2.2 million records and a data size of about 5.6 GB (index another 3.5 GB). When I test a query that uses fulltext matching, the first run takes about 15-16 seconds to complete. The second run takes about 0.1 sec and subsequent runs will all all ge around the 0.1 sec time. The query that I use has a call to NOW() as one of the criteria, so i know that the query results will not be cached. Yet, the times would suggest that some sort of caching effect is being observed. Is there something I can do that can return more consistent query performance - hopefully with a time somewhere between the two extremes? Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike snip I have read about sphinx and the good performance boost it provides - unfortunately there is a lot of legacy code reading off the db, so I will need to get all sorts of stuff signed off, before I can make any major changes ;). But I will definitely look into it so that I can offer it as a possible solution. Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
