List:General Discussion« Previous MessageNext Message »
From:Thomas Lund Date:April 12 1999 1:23pm
Subject:Considerable slowdown when selecting a text fiel
View as plain text  
I have a query where i select some fields, and it is slow. After
removing the select for a text field it is very much faster. Can I do
this is another way?

The select I use is:

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


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


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

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

And returns:
21 rows in set (2.29 sec)

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

so all joins have some kind of index/key

datadb looks like

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

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

Thank you!

/Thomas

-- 
Carl Bro as, Internet Design, Granskoven 8, 2600 Glostrup
Direct phone  : 43 48 62 56	Gen. phone    : 43 48 60 60
Dept. fax     : 43 48 68 10	Gen. fax      : 43 48 66 60
Thread
Considerable slowdown when selecting a text fielThomas Lund12 Apr
  • Considerable slowdown when selecting a text fielMichael Widenius13 Apr