View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0020139 | mantisbt | performance | public | 2015-09-25 16:45 | 2016-06-12 00:43 |
| Reporter | cproensa | Assigned To | cproensa | ||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | 1.3.0-beta.3 | ||||
| Target Version | 1.3.0-rc.2 | Fixed in Version | 1.3.0-rc.2 | ||
| Summary | 0020139: reduce print_subproject_option_list sql query count | ||||
| Description | in "print_api.php" prints the option list of subprojects one by one, each call may generate a sql query for individual project if project row is not loaded in cache. This is the case of rendering the project selector, which is usually on the first elements in a page. (note that print_project_option_list does prefetch project list into cache before iterating them) | ||||
| Tags | No tags attached. | ||||
| Attached Files | dblog_cached_rows.txt (2,325 bytes)
DB-20 0.0009 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (122,6,82,113,130,9,73,8,7) DB-21 0.0011 user_api.php:1043 user_get_accessible_subprojects() SELECT DISTINCT p.id, p.name, ph.parent_id FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 AND ph.parent_id IS NOT NULL ORDER BY p.name DB-22 0.0014 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (129,126,124,123,127,125) DB-23 0.0018 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (54,11,62,17,81,117,108,104,109,68,70,79,78,16,116,12,111) DB-24 0.0015 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (15,13,14) DB-25 0.0014 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (84,83,135,85) DB-26 0.0017 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (114,115) DB-27 0.0101 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (106,23,22,80,58,75,138,112,67,76,25,77,91,56,92,21,61,107,140) DB-28 0.0023 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (137) DB-29 0.0011 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (51,59,29,27,26,131) DB-30 0.0020 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (66) DB-31 0.0011 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (141) DB-32 0.0012 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (90) DB-33 0.0015 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (88,36,37,38,49,39,40,41,89,45,47) DB-34 0.0018 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (69,31,33,119) DB-35 0.0013 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (98,96,101,18,72,100,95,99,133,97) DB-36 0.0011 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (121) dblog_not_cached.txt (9,138 bytes)
DB-20 0.0007 project_api.php:147 project_cache_array_rows() SELECT * FROM mantis_project_table WHERE id IN (122,6,82,113,130,9,73,8,7) DB-21 0.0007 user_api.php:1043 user_get_accessible_subprojects() SELECT DISTINCT p.id, p.name, ph.parent_id FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 AND ph.parent_id IS NOT NULL ORDER BY p.name DB-22 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=129 DB-23 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=126 DB-24 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=124 DB-25 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=123 DB-26 0.0010 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=127 DB-27 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=125 DB-28 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=54 DB-29 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=11 DB-30 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=62 DB-31 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=17 DB-32 0.0010 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=81 DB-33 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=117 DB-34 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=108 DB-35 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=104 DB-36 0.0013 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=109 DB-37 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=68 DB-38 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=70 DB-39 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=79 DB-40 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=78 DB-41 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=16 DB-42 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=116 DB-43 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=12 DB-44 0.0008 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=15 DB-45 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=13 DB-46 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=14 DB-47 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=111 DB-48 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=84 DB-49 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=83 DB-50 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=135 DB-51 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=85 DB-52 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=114 DB-53 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=115 DB-54 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=106 DB-55 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=23 DB-56 0.0010 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=22 DB-57 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=80 DB-58 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=58 DB-59 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=75 DB-60 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=138 DB-61 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=137 DB-62 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=112 DB-63 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=67 DB-64 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=76 DB-65 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=25 DB-66 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=77 DB-67 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=91 DB-68 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=56 DB-69 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=92 DB-70 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=21 DB-71 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=51 DB-72 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=59 DB-73 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=29 DB-74 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=27 DB-75 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=26 DB-76 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=131 DB-77 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=66 DB-78 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=61 DB-79 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=107 DB-80 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=140 DB-81 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=141 DB-82 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=90 DB-83 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=88 DB-84 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=36 DB-85 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=37 DB-86 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=38 DB-87 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=49 DB-88 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=69 DB-89 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=31 DB-90 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=33 DB-91 0.0006 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=119 DB-92 0.0005 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=39 DB-93 0.0017 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=40 DB-94 0.0013 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=41 DB-95 0.0008 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=89 DB-96 0.0010 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=45 DB-97 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=47 DB-98 0.0008 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=98 DB-99 0.0007 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=96 DB-100 0.0011 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=101 DB-101 0.0011 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=18 DB-102 0.0012 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=72 DB-103 0.0012 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=100 DB-104 0.0008 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=95 DB-105 0.0008 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=99 DB-106 0.0009 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=133 DB-107 0.0010 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=97 DB-108 0.0010 project_api.php:106 project_cache_row() SELECT * FROM mantis_project_table WHERE id=121 | ||||
|
MantisBT: master 770df68e 2015-09-25 12:37 Committer: dregad Details Diff |
Improve sql performance print_project_option_list Cache subprojects before processing them to reduce the number of SQL queries executed. Previously, only first level projects got cached, resulting in repeated queries (one for each project). Fixes 0020139 Signed-off-by: Damien Regad <dregad@mantisbt.org> |
Affected Issues 0020139 |
|
| mod - core/print_api.php | Diff File | ||
