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
