List:Bugs« Previous MessageNext Message »
From:Jan Dvorak Date:March 17 2000 1:48pm
Subject:Re: LIKE '%xxx%' much faster on BLOBs than on TEXTs (Win only)
View as plain text  
Florian,

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.

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?

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.
Thread
LIKE '%xxx%' much faster on BLOBs than on TEXTs (Win only)Florian Kusche17 Mar
  • Re: LIKE '%xxx%' much faster on BLOBs than on TEXTs (Win only)Jan Dvorak17 Mar
  • Re: LIKE '%xxx%' much faster on BLOBs than on TEXTs (Win only)Florian Kusche17 Mar
    • Re: LIKE '%xxx%' much faster on BLOBs than on TEXTs (Win only)Thimble Smith17 Mar
  • Re: LIKE '%xxx%' much faster on BLOBs than on TEXTs (Win only)Jan Dvorak21 Mar