Database (over) usage

(Rant about drupal not django btw)
Since creating this website (in django) ive been interested in how it uses the database, dajango doesnt provide an easy way to show this on a per page basis, so instead i chose to use the log feature in MYSQL.

To enable it add log=/var/log/mysqld.log to your my.cnf file in the [mysqld] section and then restart the database.
You may not want to leave this on indefinably, as it spits out every database query (leading to a large logfile)

For this website django uses 3 queries at a minimum in reading its django_session, auth_user and auth_session tables.. as well as a few of mine (for the front page 1 per post to determin the comment count :S among others).

But to the point.. i though about seeing what other sites of mine are doing with the database, so i go to a drupal site (v4 i think), and it used 30(!) queries to view the front page, here they are:

15 Connect     ***@localhost on 
15 Init DB     ***
15 Query       SET NAMES "utf8"
15 Query       SELECT sid FROM sessions WHERE sid = '42343b68fe6195a44c3ac5cab785112a'
15 Query       SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = '42343b68fe6195a44c3ac5cab785112a'
15 Query       SELECT * FROM access WHERE status = 1 AND type = 'host' AND LOWER('80.42.179.230') LIKE LOWER(mask)
15 Query       SELECT * FROM access WHERE status = 0 AND type = 'host' AND LOWER('80.42.179.230') LIKE LOWER(mask)
15 Query       SELECT data, created, headers, expire FROM cache WHERE cid = 'variables'
15 Query       SELECT COUNT(pid) FROM url_alias
15 Query       SELECT src FROM url_alias WHERE dst = 'node/1'
15 Query       SELECT name, filename, throttle, bootstrap FROM system WHERE type = 'module' AND status = 1 
                     ORDER BY weight ASC, filename ASC 080708 18:15:18
15 Query       SELECT data, created, headers, expire FROM cache WHERE cid = 'menu:0:en'
15 Query       SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, 
                     n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture,
                     u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = 1
15 Query       SELECT DISTINCT(p.perm) FROM role r INNER JOIN permission p ON p.rid = r.rid WHERE r.rid IN (1)
15 Query       SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v 
                     ON t.vid = v.vid WHERE r.nid = 1 ORDER BY v.weight, t.weight, t.name
15 Query       SELECT * FROM files f INNER JOIN file_revisions r ON f.fid = r.fid WHERE r.vid = 1 ORDER BY f.fid
15 Query       SELECT COUNT(*) FROM node_access WHERE (nid = 0 OR nid = 1) AND ((gid = 0 AND realm = 'all')) AND grant_view >= 1
15 Query       SELECT COUNT(*) FROM node_access WHERE (nid = 0 OR nid = 1) AND ((gid = 0 AND realm = 'all')) AND grant_update >= 1
15 Query       SELECT COUNT(*) FROM node_access WHERE (nid = 0 OR nid = 1) AND ((gid = 0 AND realm = 'all')) AND grant_delete >= 1
15 Query       SELECT data, created, headers, expire FROM cache WHERE cid = 'filter:1:fde910b5fc1668eb139b1e79fa2e7501'
15 Query       SELECT * FROM system WHERE type = 'theme'
15 Query       SELECT dst FROM url_alias WHERE src = 'user/1'
15 Query       SELECT * FROM blocks WHERE theme = 'marvin' AND status = 1 ORDER BY region, weight, module
15 Query       SELECT dst FROM url_alias WHERE src = 'node/1'
15 Query       SELECT dst FROM url_alias WHERE src = 'node/5'
15 Query       SELECT dst FROM url_alias WHERE src = 'contact'
15 Query       SELECT dst FROM url_alias WHERE src = 'user/register'
15 Query       SELECT dst FROM url_alias WHERE src = 'user/password'
15 Query       UPDATE node_counter SET daycount = daycount + 1, totalcount = totalcount + 1, timestamp = 1215562518 WHERE nid = 1
15 Query       INSERT INTO accesslog (title, path, url, hostname, uid, sid, timer, timestamp) values('Frontpage', 'node/1', '', 
                     '1.2.3.4', 0, '42343b68fe6195a44c3ac5cab785112a', 118, 1215562518)
15 Query       SELECT sid FROM sessions WHERE sid = '42343b68fe6195a44c3ac5cab785112a'
15 Query       UPDATE sessions SET uid = 0, cache = 0, hostname = '1.2.3.4', session = '', timestamp = 1215562518 
                     WHERE sid = '42343b68fe6195a44c3ac5cab785112a'
 15 Quit

Ridiculous huh?
Its only displaying 2 lines of text (plus the menu)..

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *