I have UDMSearch installed on an old P166/64MB and after experiencing
problems with DBMode cache (I will get you some debug info from my test
server Alexander), I decided to configure the server to use DBMode
crc-multi.
Once I had indexed my 100,000 documents I was getting search times of 3
minutes for a three word query :o( so I had a look at the
performance.txt file included with 3.1.7, originally written by Randy
Winch <[EMAIL PROTECTED]>.
I decided to try it out and had a look through some of the docs on the
MySQL site and eventually came up with a script to optimise the tables
and the same search now takes about 4 seconds :o)
The script has only been used on RedHat 6.2 / UDMSearch 3.1.7 / MySQL
3.22.23 but if you want to use it then feel free....
Here it is:
# getyn function to provide a yes no prompt before running the script
getyn () {
while echo -n "$* (y/n) ? " >&2
do
read yn rest
case $yn in
[yY]) return 0 ;;
[nN]) return 1 ;;
*) echo -n "Please answer y or n, " >&2 ;;
esac
done
}
###################################################################
# Warn user about database ccorruption before proceeding
echo -n "Warning, "
getyn "executing this script could destroy your database, do you wish to
continue?" || exit
set -x
# Define the system parameters
MYSQLPATH=/home/mysql
BINPATH=$MYSQLPATH/bin
DATAPATH=$MYSQLPATH/var
DATANAME=udmsearch
# Loop through the table names for crc-multi mode UDMSearch indexes
for TABLE in 2 3 4 5 6 7 8 9 10 11 12 16 32 ;
do
# Export the data from the current table to a text file
echo "select * from ndict$TABLE into outfile
'$DATAPATH/ndict$TABLE.txt';" | $BINPATH/mysql $DATANAME
# Sort the data into word order (column two sort) and
output it to a .srt file
sort -k 2 $DATAPATH/ndict$TABLE.txt -o
$DATAPATH/ndict$TABLE.srt
# Remove the original text file
rm -f $DATAPATH/ndict$TABLE.txt
# Drop the tcurrent table - in MYSQL this actually
deletes the table from disk
echo "DROP TABLE ndict$TABLE;" | $BINPATH/mysql
$DATANAME
# Re-create the table based on the crc-multi create file
echo "CREATE TABLE ndict$TABLE ( url_id int(11) DEFAULT
'0' NOT NULL, word_id int(11) DEFAULT '0' NOT NULL, intag tinyint(4)
DEFAULT '0' NOT NULL, KEY url_id$TABLE (url_id), KEY word_id$TABLE
(word_id) );" | $BINPATH/mysql $DATANAME
# Flush the tables
$BINPATH/mysqladmin flush-tables
# Disable indexes
$BINPATH/isamchk --keys-used=0 -rq
$DATAPATH/$DATANAME/ndict$TABLE
# Restart the database server
/etc/rc.d/init.d/mysql stop
sleep 5
/etc/rc.d/init.d/mysql start
sleep 5
# Load the sorted text file into the table
echo "LOAD DATA INFILE '$DATAPATH/ndict$TABLE.srt' INTO
TABLE ndict$TABLE;" | $BINPATH/mysql $DATANAME
# Restart the database server
/etc/rc.d/init.d/mysql stop
sleep 5
/etc/rc.d/init.d/mysql start
sleep 5
# Rebuild indexes
$BINPATH/isamchk -r -q $DATAPATH/$DATANAME/ndict$TABLE
# Flush the tables
$BINPATH/mysqladmin flush-tables
# Delete the sorted text file
rm -f $DATAPATH/ndict$TABLE.srt
done
______________
If you want to unsubscribe send "unsubscribe udmsearch"
to [EMAIL PROTECTED]
