Something's not right. In my database, it take about 3 secs to do a single word search, but doing a double word takes an average of 68 seconds. I'm sure this wasn't the case before the upgrade to 2.1.0.

The database contains 100,000 links and runs under mod_perl. At this speed the database is unusable from a visitors point of view.

~ ERASER

---

Free JavaScripts @ Insight Eye

Goto Database->Links->Properties and look at what Search Mode is set to. Make sure it is set to INTERNAL, and not NONINDEXED. You may need to update it from shell on large databases using nph-index.cgi:

./nph-index.cgi --change-driver=INTERNAL

You can also try the built in MySQL full text indexes for quick searches too by doing:

./nph-index.cgi --change-driver=MYSQL

Cheers,

Alex
--
Gossamer Threads Inc.

I had INTERNAL as the indexing schema but tried setting that again and re-indexing the search. A 2 word search is now taking 164.56 seconds for a result.

I'm going to install a copy on my test server using the same database to see if the main install was correct. This shouldn't be like this, so something is obviously amiss.

Thanks.

~ ERASER

---

Free JavaScripts @ Insight Eye

Hi,

While the search is running, see if you can do a `mysqladmin proc` to see what processes are running? It sounds like something is locking the table if it's taking that long.

Cheers,

Alex
--
Gossamer Threads Inc.

Well, after much investigation, I have narrowed it down to the Boolean operator - bool=and.

Running with this command takes over 1 minute and according to 'top' mysqld is at 95% CPU & search.cgi at 55% CPU.

Now the weird part, this only happens on the main domain. On a test domain running the SAME setup and database the search works fine with bool=and (0.5 seconds)

Proc says:

| Id | User | Host | db | Command | Time | State | Info
+-------+------+-----------+-----------+---------+------+------------+-----------------
22353 | root | localhost | axb_ldata | Query | 77 | statistics | SELECT Item_ID, SUM(Score) AS Score

This has me really stumped and with a search engine no one can use!

~ ERASER

---

Free JavaScripts @ Insight Eye

Forget my last post about both installs being the same. In fact, there was 1 slight difference that has shown me the main problem with double word searches taking so long:

search_substring ON

- that was the big problem. With this setting to off, all searches were returning correctly.

Phew! This has cost me 2 days of downtime

As this was always set to ON in my last version without any problems, may I surmise there is an issue or bug with this option and the internal indexing schema?

~ ERASER

---

Free JavaScripts @ Insight Eye

Hi,

Is there any chance you could do a:

mysqldump -uUSER -pPASS database > output.txt

and send me ftp info to get that file so we can do some internal testing here to see why the major slowdown? Search substrings on our sample databases (of about 80,000 links) does not show anywhere near that sort of performance bottleneck.

Cheers,

Alex
--
Gossamer Threads Inc.