Hello,
I found an interesting performance problem.
First, let me tell you, that this is not critical for me, since I found
a workaround for the problem. However, I think you might be interested
(if you don't know it already).
I'm sorry that I didn't use the mysqlbug program, but it seems, that it
is not included in the win32 distribution.
This is the system I use:
- mysqld-nt binary 3.22.32 (not the shareware)
- windows 2000 pro
- 128 mb ram
- pentium2 266 MHz
I want to set up a full text search for our web based price list.
For that, I'm using a table containing a single column of type "TEXT".
This table contains about 40000 rows of data.
The longest entry has 1754 bytes.
The SELECT statement I used in order to test is
SELECT suchdaten FROM artikel_suchdaten
WHERE suchdaten LIKE "%asus%"
AND suchdaten LIKE "%intel%"
This query takes about 17 seconds on my machine.
This applies to all mysqld-variations (mysqld, mysqld-nt and
mysqld-opt).
mysql 3.22.32 for linux (glibc, the binary from your webpage)
needs about 0.4 seconds for the same query.
However, if I use a "BLOB"-column instead of "TEXT", the win32-server
will be as fast as the linux server (about 0.3 to 0.4 seconds). This is
the workaround I use.
Below is some more information.
Regards,
Flo
C:\mysql\bin>mysqladmin version
mysqladmin Ver 8.0 Distrib 3.22.32, for Win95/Win98 on i586
TCX Datakonsult AB, by Monty
Server version 3.22.32
Protocol version 10
Connection . via named pipe
UNIX socket MySQL
Uptime: 8 min 32 sec
Threads: 1 Questions: 10 Slow queries: 0 Opens: 6 Flush tables: 1
Open tables: 3
Directory of C:\mysql\data\webprl
16.03.2000 16:12 8.564 artikel_suchdaten.frm
16.03.2000 16:37 6.917.876 artikel_suchdaten.isd
16.03.2000 16:37 1.024 artikel_suchdaten.ism
C:\>echo describe artikel_suchdaten | mysql -vvv webprl
--------------
describe artikel_suchdaten
--------------
+-----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| suchdaten | blob | | | NULL | |
+-----------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
s/blob/text/ for the slow case
C:\mysql\bin>mysqldump --no-data webprl artikel_suchdaten
# MySQL dump 7.1
#
# Host: localhost Database: webprl
#--------------------------------------------------------
# Server version 3.22.32
#
# Table structure for table 'artikel_suchdaten'
#
CREATE TABLE artikel_suchdaten (
suchdaten blob NOT NULL
);
s/blob/text/ for the slow case
