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