There is a one query which makes pligg slow. Found a way to speed it up a lot.
Was playing around MySQL optimization on pligg. Found one key which
improves things a lot. Query is ran on showing story related news. Let
see, I do it before and after:
mysql> SELECT link_id,digg_links.link_title, digg_links.link_title_url,
COUNT( digg_tags.tag_link_id ) AS relevance, digg_tags.tag_link_id FROM
digg_tags, digg_links WHERE digg_tags.tag_words IN ( 'prygi',
'voistlus', 'oko', 'leedu', 'eesti' ) AND digg_tags.tag_link_id =
digg_links.link_id AND digg_links.link_status != 'discard' AND NOT
digg_links.link_id = 334 GROUP BY digg_tags.tag_link_id,
digg_links.link_title, digg_links.link_title_url ORDER BY relevance DESC
LIMIT 10;
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
| link_id | link_title
| link_title_url
| relevance | tag_link_id |
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
| 16047 | Rumeenlased hakkasid Baltimaade õhuruumi valvama
| Rumeenlased_hakkasid_Baltimaade_ohuruumi_valvama
| 2 | 16047 |
| 39717 | Leedulased tahavad prügikoristamises eestlastele ära teha
| leedulased-tahavad-prygikoristamises-eestlastele-ara-teha
| 2 | 39717 |
| 104 | Soomlased põletavad Eesti lippu
| Soomlased-pletavad-Eesti-lippu
| 1 | 104 |
| 184 | Valimisjaoskonda külastab Esto TV
| Valimisjaoskonda-klastab-Esto-TV
| 1 | 184 |
| 219 | Elu surnukuuri eesruumis
| Elu-surnukuuri-eesruumis
| 1 | 219 |
| 239 | Harju Maakohus kohustab IANA(Internet Assigned Numbers
Authorty).... |
Harju-Maakohus-kohustab-IANAInternet-Assigned-Numbers-Authorty
| 1 | 239 |
| 250 | Eesti mehe munandi suurus ja spermatosoidide hulk on
Euroopas juhtival kohal. |
Eesti_mehe_munandi_suurus_ja_spermatosoidide_hulk_ on_Euroopas_juhtival_kohal- | 1 | 250 |
| 309 | Eesti saite kutsutakse üles ründama
| Eesti-saite-kutsutakse-les-rndama
| 1 | 309 |
| 311 | 70 noort sulges Eesti-Vene piiri pooleks tunniks
| 70-noort-sulges-EestiVene-piiri-pooleks-tunniks
| 1 | 311 |
| 312 | Venemaa nõuab oma kodanike vabastamist
| Venemaa-nuab-oma-kodanike-vabastamist
| 1 | 312 |
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
10 rows in set (0.61 sec)
mysql>
Half second! It gets query cached here but anyway it is slow.
Let see why it is slow:
mysql> explain SELECT link_id,digg_links.link_title,
digg_links.link_title_url, COUNT( digg_tags.tag_link_id ) AS relevance,
digg_tags.tag_link_id FROM digg_tags, digg_links WHERE
digg_tags.tag_words IN ( 'prygi', 'voistlus', 'oko', 'leedu', 'eesti' )
AND digg_tags.tag_link_id = digg_links.link_id AND
digg_links.link_status != 'discard' AND NOT digg_links.link_id = 334
GROUP BY digg_tags.tag_link_id, digg_links.link_title,
digg_links.link_title_url ORDER BY relevance DESC LIMIT 10;
+----+-------------+------------+------+---------------+-------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra
|
+----+-------------+------------+------+---------------+-------------+---------+-------------------------+------+----------------------------------------------+
| 1 | SIMPLE | digg_links | ALL | PRIMARY | NULL |
NULL | NULL | 4703 | Using where; Using temporary;
Using filesort |
| 1 | SIMPLE | digg_tags | ref | tag_link_id | tag_link_id | 4
| digg.digg_links.link_id | 1 | Using where; Using index
|
+----+-------------+------------+------+---------------+-------------+---------+-------------------------+------+----------------------------------------------+
2 rows in set (0.01 sec)
mysql>
Oh fu*k! It goes via 4703 rows to examine them
Adding one index:
mysql> alter table digg_tags add key tag_words (`tag_words`);Query OK,
14394 rows affected (14.24 sec)Records: 14394 Duplicates: 0 Warnings:
0mysql>
mysql> SELECT link_id,digg_links.link_title, digg_links.link_title_url,
COUNT( digg_tags.tag_link_id ) AS relevance, digg_tags.tag_link_id FROM
digg_tags, digg_links WHERE digg_tags.tag_words IN ( 'prygi',
'voistlus', 'oko', 'leedu', 'eesti' ) AND digg_tags.tag_link_id =
digg_links.link_id AND digg_links.link_status != 'discard' AND NOT
digg_links.link_id = 334 GROUP BY digg_tags.tag_link_id,
digg_links.link_title, digg_links.link_title_url ORDER BY relevance DESC
LIMIT 10;
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
| link_id | link_title
| link_title_url
| relevance | tag_link_id |
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
| 16047 | Rumeenlased hakkasid Baltimaade õhuruumi valvama
| Rumeenlased_hakkasid_Baltimaade_ohuruumi_valvama
| 2 | 16047 |
| 39717 | Leedulased tahavad prügikoristamises eestlastele ära teha
| leedulased-tahavad-prygikoristamises-eestlastele-ara-teha
| 2 | 39717 |
| 104 | Soomlased põletavad Eesti lippu
| Soomlased-pletavad-Eesti-lippu
| 1 | 104 |
| 184 | Valimisjaoskonda külastab Esto TV
| Valimisjaoskonda-klastab-Esto-TV
| 1 | 184 |
| 219 | Elu surnukuuri eesruumis
| Elu-surnukuuri-eesruumis
| 1 | 219 |
| 239 | Harju Maakohus kohustab IANA(Internet Assigned Numbers
Authorty).... |
Harju-Maakohus-kohustab-IANAInternet-Assigned-Numbers-Authorty
| 1 | 239 |
| 250 | Eesti mehe munandi suurus ja spermatosoidide hulk on
Euroopas juhtival kohal. |
Eesti_mehe_munandi_suurus_ja_spermatosoidide_hulk_ on_Euroopas_juhtival_kohal- | 1 | 250 |
| 309 | Eesti saite kutsutakse üles ründama
| Eesti-saite-kutsutakse-les-rndama
| 1 | 309 |
| 311 | 70 noort sulges Eesti-Vene piiri pooleks tunniks
| 70-noort-sulges-EestiVene-piiri-pooleks-tunniks
| 1 | 311 |
| 312 | Venemaa nõuab oma kodanike vabastamist
| Venemaa-nuab-oma-kodanike-vabastamist
| 1 | 312 |
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
10 rows in set (0.08 sec)
mysql>
MUCH BETTER! It can use indexes now:
mysql> explain SELECT link_id,digg_links.link_title,
digg_links.link_title_url, COUNT( digg_tags.tag_link_id ) AS relevance,
digg_tags.tag_link_id FROM digg_tags, digg_links WHERE
digg_tags.tag_words IN ( 'prygi', 'voistlus', 'oko', 'leedu', 'eesti' )
AND digg_tags.tag_link_id = digg_links.link_id AND
digg_links.link_status != 'discard' AND NOT digg_links.link_id = 334
GROUP BY digg_tags.tag_link_id, digg_links.link_title,
digg_links.link_title_url ORDER BY relevance DESC LIMIT 10;
+----+-------------+------------+--------+-----------------------+-----------+---------+----------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+------------+--------+-----------------------+-----------+---------+----------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | digg_tags | range | tag_link_id,tag_words |
tag_words | 66 | NULL | 181 | Using where;
Using index; Using temporary; Using filesort |
| 1 | SIMPLE | digg_links | eq_ref | PRIMARY |
PRIMARY | 4 | digg.digg_tags.tag_link_id | 1 | Using where
|
+----+-------------+------------+--------+-----------------------+-----------+---------+----------------------------+------+-----------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>