>>>>> "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
