List:Bugs« Previous MessageNext Message »
From:Florian Kusche Date:March 17 2000 1:24pm
Subject:LIKE '%xxx%' much faster on BLOBs than on TEXTs (Win only)
View as plain text  
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
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