From: Michael Widenius
Date: April 13 1999 12:50am
Subject: Considerable slowdown when selecting a text fiel
List-Archive: http://lists.mysql.com/mysql/1739
Message-Id: <14098.37817.116972.682550@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Thomas" == Thomas Lund <tld@stripped> writes:

Thomas> I have a query where i select some fields, and it is slow. After
Thomas> removing the select for a text field it is very much faster. Can I do
Thomas> this is another way?

Thomas> The select I use is:

Thomas> select
Thomas> datadb.txt,datadb.title,datadb.meta_description,urldb.url,urldb.parenturlid,urldb.myframename,urldb.id,datadb.language
Thomas> from ordliste, datadb left join urldb on datadb.urlid=urldb.id left join
Thomas> relordurl on datadb.urlid=relordurl.urlid 
Thomas> where datadb.urlid IN
Thomas> (6,24,26,28,35,36,37,39,40,58,71,78,95,123,145,208,211,213,221,226,228)
Thomas> and relordurl.ordid=ordliste.ordid 
Thomas> group by urldb.id 
Thomas> order by urldb.parenturlid,datadb.title


Thomas> And that returns:
Thomas> 21 rows in set (11.69 sec)


Thomas> When deleting the datadb.txt entry in the select, the same query runs
Thomas> much faster

Thomas> select
Thomas> datadb.title,datadb.meta_description,urldb.url,urldb.parenturlid,urldb.myframename,urldb.id,datadb.language
Thomas> from ordliste, datadb left join urldb on datadb.urlid=urldb.id left join
Thomas> relordurl on datadb.urlid=relordurl.urlid 
Thomas> where datadb.urlid IN
Thomas> (6,24,26,28,35,36,37,39,40,58,71,78,95,123,145,208,211,213,221,226,228)
Thomas> and relordurl.ordid=ordliste.ordid 
Thomas> group by urldb.id 
Thomas> order by urldb.parenturlid,datadb.title

Thomas> And returns:
Thomas> 21 rows in set (2.29 sec)

Thomas> explain returns:
Thomas> +-----------+--------+---------------+---------+---------+-----------------+------+-------------+
Thomas> | table     | type   | possible_keys | key     | key_len |
Thomas> ref             | rows | Extra       |
Thomas> +-----------+--------+---------------+---------+---------+-----------------+------+-------------+
Thomas> | datadb    | range  | PRIMARY,urlid | PRIMARY |    NULL |
Thomas> NULL            |   21 |             |
Thomas> | urldb     | eq_ref | PRIMARY,id    | PRIMARY |       4 |
Thomas> datadb.urlid    |    1 |             |
Thomas> | relordurl | ref    | urlid         | urlid   |       4 |
Thomas> datadb.urlid    |  304 |             |
Thomas> | ordliste  | eq_ref | PRIMARY       | PRIMARY |       4 |
Thomas> relordurl.ordid |    1 | Using index |
Thomas> +-----------+--------+---------------+---------+---------+-----------------+------+-------------+

Thomas> so all joins have some kind of index/key

Thomas> datadb looks like

mysql> show fields from datadb;
Thomas> +------------------+---------------+------+-----+---------+-------+
Thomas> | Field            | Type          | Null | Key | Default | Extra |
Thomas> +------------------+---------------+------+-----+---------+-------+
Thomas> | urlid            | int(8)        |      | PRI | 0       |       |
Thomas> | title            | varchar(255)  | YES  |     | NULL    |       |
Thomas> | content_type     | varchar(255)  | YES  |     | NULL    |       |
Thomas> | language         | char(2)       | YES  |     | NULL    |       |
Thomas> | txt              | mediumtext    | YES  |     | NULL    |       |
Thomas> | meta_keywords    | text          | YES  |     | NULL    |       |
Thomas> | meta_description | text          | YES  |     | NULL    |       |
Thomas> | datoAendret      | timestamp(14) | YES  |     | NULL    |       |
Thomas> +------------------+---------------+------+-----+---------+-------+

Thomas> Any idea whats wrong, and why this is so darn slow?

Thomas> Thank you!

Thomas> /Thomas

Hi!

The only penalty for 'text' in cases like the above should be the time
it takes to send the text to the client.

Does EXPLAIN return the same output for both queries ?
How long are the total txt lengths ?
Have you compiled MySQL with or without debugging?
How big are the tables?  Any change you can ftp a test case to:
ftp://www.mysql.com/pub/mysql/secret ?

Regards,
Monty