Hello Jan,
> A potential explanation is that the table with the TEXT field
> on your NT system is severely fragmented. This can occurr
> e.g. if you add records by an insert, and then you update
> the contents of the TEXT field. Or if you had some deletes.
I don't think so. I did the following:
- CREATE TABLE
- INSERT all data
- do the select
If I have TEXT-columns, it's slow and with BLOB columns it's fast.
> When you copied the table to another server,
> or to another table with the BLOB field,
> the fragmentation is not reproduced there --
> the data are inserted sequentially into tables created afresh.
>
> What happens when you run 'isamchk -r' on the original table?
no difference
cu
Flo
>
> Jan
>
> P.S.
> There may be also other causes for the slow-down.
> I'm offering this as just a partial explanation.
>
> Florian Kusche wrote:
> >
> > 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
> >
> > ---------------------------------------------------------------------
> > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> > posting. To request this thread, e-mail bugs-thread48@stripped
> >
> > To unsubscribe, send a message to the address shown in the
> > List-Unsubscribe header of this message. If you cannot see it,
> > e-mail bugs-unsubscribe@stripped instead.
