Repository :
http://git.fedorahosted.org/cgit/
On branch : master
---------------------------------------------------------------
commit cce3a7345719b0b0f719936a8d055898f230983f
Author: Matt Domsch <matt(a)domsch.com>
Date: Thu Jul 25 16:28:08 2013 -0500
model: speed up publiclist_hosts() greatly
This query could take minutes to execute, as it was essentially
joining the entire database together across all the critical
relations. It was doing a naked join on both host_category_dir (so
the results had all host_category_dirs tested, only to throw away all
that data), as well as a naked join on host_category +
category_directory + directory.
I realized we could use a subquery, LIMIT 1, with EXISTS to handle
those naked joins cleanly. That's what this does.
Queries that used to take minutes now take ~10 seconds, and queries
thereafter on the same or similar queries now take 0.1s, much
improved.
This will speed up generate-publiclist a bit, and reduce load on the
database.
---------------------------------------------------------------
server/mirrormanager/model.py | 67 ++++++++++++++++++++++++----------------
1 files changed, 40 insertions(+), 27 deletions(-)
diff --git a/server/mirrormanager/model.py b/server/mirrormanager/model.py
index 54bd2f9..ff86a12 100644
--- a/server/mirrormanager/model.py
+++ b/server/mirrormanager/model.py
@@ -507,12 +507,22 @@ def _publiclist_sql_to_list(sqlresult, valid_categories):
return l
def _publiclist_hosts(product=None, re=None):
+ class MY_RLIKE(RLIKE):
+ def __sqlrepr__(self, db):
+ return "(%s %s (%s))" % (
+ self.string, self._get_op(db), sqlrepr(self.expr, db))
+
+ def _rlike(pattern, string):
+ # there's probably a beter way to get this, but this works...
+ _dburi = config.get('sqlobject.dburi', 'sqlite://')
+ dbtype = _dburi.strip('notrans_').split(':')[0]
+ return MY_RLIKE(pattern, string).__sqlrepr__(dbtype) + " "
sql1_filter = ''
sql2_filter = ''
- sql_common_select = "SELECT DISTINCT host.id, host.country, host.name AS
host_name, host.bandwidth_int, host.comment, host.internet2, site.org_url, site.name AS
site_name, category.name AS category_name, host_category_url.url "
- sql1_from = "FROM category, host_category, host, site, host_category_url,
host_category_dir "
- sql2_from = "FROM category, host_category, host, site, host_category_url,
directory, category_directory "
+ sql_common_select = "SELECT DISTINCT host.id, host.country, host.name AS
host_name, host.bandwidth_int, host.comment, host.internet2, "
+ sql_common_select += "site.org_url, site.name AS site_name, category.name AS
category_name, host_category_url.url "
+ sql_common_from = "FROM category, host_category, host, site,
host_category_url "
# join conditions
sql_common = "WHERE "
sql_common += "host_category.category_id = category.id AND "
@@ -521,41 +531,44 @@ def _publiclist_hosts(product=None, re=None):
if product is not None:
sql_common += "category.product_id = %s AND " % product.id
sql_common += "host.site_id = site.id "
- # select conditions
- # up2date, active, not private
+ # select conditions
# up2date, active, not private
sql_common += 'AND host.user_active AND site.user_active '
sql_common += 'AND host.admin_active AND site.admin_active '
sql_common += 'AND NOT host.private '
sql_common += 'AND NOT site.private '
sql_common += 'AND NOT host_category_url.private '
sql_common += 'AND category.publiclist '
-
- sql1_join = 'AND host_category_dir.host_category_id = host_category.id '
- sql2_join = 'AND category_directory.directory_id = directory.id '
- sql2_join += 'AND category_directory.category_id = category.id '
-
- class MY_RLIKE(RLIKE):
- def __sqlrepr__(self, db):
- return "(%s %s (%s))" % (
- self.string, self._get_op(db), sqlrepr(self.expr, db))
-
- def _rlike(pattern, string):
- # there's probably a beter way to get this, but this works...
- _dburi = config.get('sqlobject.dburi', 'sqlite://')
- dbtype = _dburi.strip('notrans_').split(':')[0]
- return MY_RLIKE(pattern, string).__sqlrepr__(dbtype) + " "
+ sql_common += 'AND EXISTS '
if re is not None:
sql1_filter = "AND " + _rlike(re, 'host_category_dir.path')
sql2_filter = "AND " + _rlike(re, 'directory.name')
- sql1_up2date = 'AND host_category_dir.up2date '
- sql2_up2date = 'AND host_category.always_up2date '
-
- sql1 = sql_common_select + sql1_from + sql_common + sql1_filter + sql1_join +
sql1_up2date
- sql2 = sql_common_select + sql2_from + sql_common + sql2_filter + sql2_join +
sql2_up2date
-
- sql = "SELECT * FROM ( %s UNION %s ) AS subquery" % (sql1, sql2)
+ sql1_subselect = '('
+ sql1_subselect += 'SELECT 1 from host_category, host_category_dir '
+ sql1_subselect += 'WHERE host_category.category_id = category.id '
+ sql1_subselect += 'AND host_category.host_id = host.id '
+ sql1_subselect += 'AND host_category_dir.host_category_id = host_category.id
'
+ sql1_subselect += 'AND host_category_dir.up2date '
+ sql1_subselect += sql1_filter
+ sql1_subselect += 'LIMIT 1 '
+ sql1_subselect += ') '
+
+ sql2_subselect = '('
+ sql2_subselect += 'SELECT 1 from host_category, category_directory, directory
'
+ sql2_subselect += 'WHERE host_category.category_id = category.id '
+ sql2_subselect += 'AND host_category.host_id = host.id '
+ sql2_subselect += 'AND category_directory.directory_id = directory.id '
+ sql2_subselect += 'AND category_directory.category_id = category.id '
+ sql2_subselect += 'AND host_category.always_up2date '
+ sql2_subselect += sql2_filter
+ sql2_subselect += 'LIMIT 1 '
+ sql2_subselect += ') '
+
+ sql1 = sql_common_select + sql_common_from + sql_common + sql1_subselect
+ sql2 = sql_common_select + sql_common_from + sql_common + sql2_subselect
+
+ sql = "SELECT * FROM ( ( %s ) UNION ( %s ) ) AS subquery" % (sql1, sql2)
result = Directory._connection.queryAll(sql)
return result