MySQL MyISAM table performance... painfully, painfully slow
- by Salman A
I've got a table structure that can be summarized as follows:
pagegroup
* pagegroupid
* name
has 3600 rows
page
* pageid
* pagegroupid
* data
references pagegroup;
has 10000 rows;
can have anything between 1-700 rows per pagegroup;
the data column is of type mediumtext and the column contains 100k - 200kbytes data per row
userdata
* userdataid
* pageid
* column1
* column2
* column9
references page;
has about 300,000 rows;
can have about 1-50 rows per page
The above structure is pretty straight forwad, the problem is that that a join from userdata to page group is terribly, terribly slow even though I have indexed all columns that should be indexed. The time needed to run a query for such a join (userdata inner_join page inner_join pagegroup) exceeds 3 minutes. This is terribly slow considering the fact that I am not selecting the data column at all. Example of the query that takes too long:
SELECT userdata.column1, pagegroup.name
FROM userdata
INNER JOIN page USING( pageid )
INNER JOIN pagegroup USING( pagegroupid )
Please help by explaining why does it take so long and what can i do to make it faster.
Edit #1
Explain returns following gibberish:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE userdata ALL pageid 372420
1 SIMPLE page eq_ref PRIMARY,pagegroupid PRIMARY 4 topsecret.userdata.pageid 1
1 SIMPLE pagegroup eq_ref PRIMARY PRIMARY 4 topsecret.page.pagegroupid 1
Edit #2
SELECT
u.field2, p.pageid
FROM
userdata u
INNER JOIN page p ON u.pageid = p.pageid;
/*
0.07 sec execution, 6.05 sec fecth
*/
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE u ALL pageid 372420
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 topsecret.u.pageid 1 Using index
SELECT
p.pageid, g.pagegroupid
FROM
page p
INNER JOIN pagegroup g ON p.pagegroupid = g.pagegroupid;
/*
9.37 sec execution, 60.0 sec fetch
*/
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE g index PRIMARY PRIMARY 4 3646 Using index
1 SIMPLE p ref pagegroupid pagegroupid 5 topsecret.g.pagegroupid 3 Using where
Moral of the story
Keep medium/long text columns in a separate table if you run into performance problems such as this one.
