JOIN performance very slow when selecting VARCHAR field

Question

I have a difficult problem with a query which I can't find out why it is performing so bad.

Please see following queries and query times (using HeidiSQL):

SELECT p.TID, a.TID
FROM characters AS p JOIN account a ON p.AccountId = a.TID;

=> rows: 57.879 Query time: 0.063 sec. (+ 0.328 sec. network)

Explain:

+----+-------------+-------+-------+---------------+--------------+---------+-----------+-------+--------------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref       | rows  | Extra                    |
+----+-------------+-------+-------+---------------+--------------+---------+-----------+-------+--------------------------+
|  1 | SIMPLE      | a     | index | TID           | WebAccountId | 5       | NULL      | 21086 | Using index              |
|  1 | SIMPLE      | p     | ref   | AccountId     | AccountId    | 5       | dol.a.TID |     1 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------------+---------+-----------+-------+--------------------------+

---

This is fast but as soon as I select a VARCHAR(255) field from table characters it gets very slow. See network time.

SELECT p.TID, a.TID, p.LastName
FROM characters AS p JOIN account a ON p.AccountId = a.TID;

=> rows: 57.879 Query time: 0.219 sec. (+ 116.234 sec. network)

+----+-------------+-------+-------+---------------+--------------+---------+-----------+-------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref       | rows  | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+-----------+-------+-------------+
|  1 | SIMPLE      | a     | index | TID           | WebAccountId | 5       | NULL      | 21086 | Using index |
|  1 | SIMPLE      | p     | ref   | AccountId     | AccountId    | 5       | dol.a.TID |     1 | Using where |
+----+-------------+-------+-------+---------------+--------------+---------+-----------+-------+-------------+

Query time is still good but network time got unbearable.

---

One could think that its caused by the transfer of p.LastName but see the query without the join:

SELECT p.TID, p.LastName
FROM characters AS p

=> rows: 57.881 Query time: 0.063 sec. (+ 0.578 sec. network)

+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | p     | ALL  | NULL          | NULL | NULL    | NULL | 59800 |       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+

Any idea what is going on here? I have no idea how to fix that.

Edit: Added the Explain output for each query. In case it matters, it's mysql 5.1.72-community

Edit2: Tested from commandline. Same performance. If I look into the mysql process list I see Sending data for the poor performing query. The query was originally used in a ASP.NET web application before and performance was very bad. That is why I used HeidiSQL to investigate. I would definitely rule out HeidiSQL as the problem.

Edit3 Test result in Mysql Workbench:

Answer 1

I found out what was the culprit here. I used mysql 5.1.72 with InnoDB on default settings.

This means it used an InnoDB buffer pool of just 8MB

innodb_buffer_pool_size=8M

Mysql was forced to write the result to disk as it couldn't hold it in memory for transfer as soon as I added the VARCHAR fields to the select clause. The Join seems to have pressured the memory usage of that buffer even more.

After I changed the buffer size to 1G the problem was gone.

innodb_buffer_pool_size=1G

The first request after mysql start can still be a bit slow but subsequent queries are very fast.

So it was basically misconfiguration of the mysql server.