Hi,
I wrote some weeks ago to the mailinglist with the same problem. The performance isn't still much better.
I try to find the optimal value for tmp_table_size (now set to 67108864), cause we have some problems. Sometimes I see, that the result data will be copied to tmp table:
Copying to tmp table | select distinct m.media_id from category_tree c_tree, media_2_category m2c, medi
This queries are slow. But in MySQLAdministrator 1.0.19 I see:
created_tmp_disk_tables = 0 created_tmp_files = 2315 created_tmp_tables = 98887
Why is MySQL copying the result set to tmp table? THe same query executed from command line is faaster than from our JBoss.
This query nees from command line 0,09-0,20 sec:
select
distinct m.media_id
from
category_tree c_tree,
media_2_category m2c,
media m,
media_2_partner m2p,
magix_product mp,
media_type_2_magix_product mt2mp,
media_file mf
where
c_tree.mandant_id = 2
and
c_tree.partner_id = 1
and
c_tree.category_tree_id = m2c.category_tree_id
and
m2c.media_id = m.media_id
and
m2p.media_id = m.media_id
and
m2p.partner_id = 1
and
mp.magix_product_id = 20
and
mp.magix_product_id = mt2mp.magix_product_id
and
m.media_type_id = mt2mp.media_type_id
and
mf.media_id = m.media_id
and
(mf.language_id = 4 or mf.language_id is null)
and
mf.media_file_quality_id = 4
and
(mf.videosignal is null or mf.videosignal = 'ntsc')
and
(c_tree.category_tree_id = 3533 or c_tree.parent_id = 3533 or c_tree.path like '3531/3533/%')
order by m2p.priority desc limit 36, 36
From JBoss 0,4-1,6 sec.:
2005-04-19 17:54:41,576 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 656 millis
2005-04-19 17:56:08,836 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 657 millis
2005-04-19 17:56:11,799 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 1320 millis
2005-04-19 17:56:11,860 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 1263 millis
2005-04-19 17:56:37,947 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 450 millis
2005-04-19 17:58:20,456 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 450 millis
2005-04-19 18:00:37,389 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 453 millis
2005-04-19 18:01:29,018 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 624 millis
2005-04-19 18:02:08,169 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 452 millis
2005-04-19 18:02:12,141 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 456 millis
2005-04-19 18:02:27,084 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 452 millis
2005-04-19 18:02:29,907 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 453 millis
2005-04-19 17:52:21,821 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 660 millis
2005-04-19 17:53:39,256 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 574 millis
2005-04-19 17:54:00,722 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 573 millis
2005-04-19 17:54:03,903 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 655 millis
2005-04-19 17:54:20,486 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 644 millis
2005-04-19 17:59:06,642 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 656 millis
2005-04-19 17:59:24,124 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 657 millis
2005-04-19 18:01:18,357 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 654 millis
2005-04-19 18:01:27,095 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 632 millis
2005-04-19 18:01:28,402 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 915 millis
2005-04-19 18:01:29,468 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 667 millis
2005-04-19 18:02:59,673 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 656 millis
2005-04-19 18:03:02,092 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 1479 millis
2005-04-19 18:03:02,689 WARN [net.mgx.component.database.MySQL] getMediaIdsForCategoryNode() slow query needed 1640 millis
In resultset are 34 long values.
The explain of the sql statement looks so (csv separated by |):
table|type|possible_keys|key|key_len|ref|rows|Extra
mp|const|PRIMARY|PRIMARY|2|const|1|Using index; Using temporary; Using filesort
mt2mp|ref|media_type_2_magix_product__idx,media_type_id,magix_product_id|magix_product_id|2|const|18|Using where
m|ref|PRIMARY,media_type_id|media_type_id|2|mt2mp.media_type_id|242|Using index
m2p|eq_ref|media_partner__idx,media_id,partner_id|media_partner__idx|10|m.media_id,const|1|Distinct
mf|ref|media_file_quality_language__idx,media_id,media_file_quality_id,language_id,media_file_quality__idx|media_file_quality_language__idx|10|m.media_id,const|1|Using where; Distinct
m2c|ref|category_tree_id,media_id|media_id|8|m.media_id|3|Distinct
c_tree|eq_ref|PRIMARY,category_tree__idx,parent_id,mandant_id,partner_id,category_tree_path__idx|PRIMARY|8|m2c.category_tree_id|1|Using where; Distinct
We are using MySQL 4.0.23a under Linux.
Regards,
Rafal
