List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 13 1999 12:50am
Subject:Considerable slowdown when selecting a text fiel
View as plain text  
>>>>> "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
Thread
Considerable slowdown when selecting a text fielThomas Lund12 Apr
  • Considerable slowdown when selecting a text fielMichael Widenius13 Apr