Optimizing Nodequeue Database tables

Events happening in the community are now at Drupal community events on www.drupal.org.
ezra-g's picture

I suspect that the tables in the Nodequeue module can be optimized, but I'm not sure how to start. Nodequeue allows people to create arbitrarily ordered lists of nodes, and consists of queues and subqueues. A subqueue is a distinct list of nodes that inherits properties from its parent queue. Nodequeue's Smartqueue API allows modules to define new types of subqueues, such as a subqueue per-user or for each taxonomy term of one or more vocabularies. Below is the desc output of nodequeue's tables. nodequeue_subqueue joins to nodequeue_queue on the qid. nodequeue_nodes often joins against the node table on nid. Any suggestions would be appreciated by me and nodequeue's decently sized user base! Thanks!

mysql> desc nodequeue_nodes;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| qid | int(10) unsigned | NO | | | |
| sqid | int(10) unsigned | NO | MUL | | |
| nid | int(10) unsigned | YES | MUL | NULL | |
| position | int(10) unsigned | YES | | NULL | |
| timestamp | int(10) unsigned | NO | | 0 | |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc nodequeue_roles;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| qid | bigint(20) unsigned | NO | MUL | | |
| rid | bigint(20) unsigned | YES | MUL | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc nodequeue_queue;
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| qid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | | | |
| subqueue_title | varchar(255) | NO | | | |
| size | int(11) | YES | | 0 | |
| link | varchar(40) | YES | | NULL | |
| link_remove | varchar(40) | YES | | NULL | |
| owner | varchar(255) | YES | | NULL | |
| show_in_ui | tinyint(4) | YES | | 1 | |
| show_in_tab | tinyint(4) | YES | | 1 | |
| show_in_links | tinyint(4) | YES | | 1 | |
| reference | varchar(255) | YES | | 0 | |
| reverse | tinyint(4) | YES | | NULL | |
| i18n | tinyint(4) | YES | | 1 | |
+----------------+------------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

mysql> desc nodequeue_subqueue;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| sqid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| qid | int(10) unsigned | NO | MUL | | |
| reference | varchar(255) | YES | MUL | 0 | |
| title | varchar(255) | YES | MUL | | |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc nodequeue_types;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| qid | bigint(20) unsigned | NO | MUL | | |
| type | varchar(255) | YES | MUL | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Comments

Reference field question

Coornail's picture

I admit that I'm not familiar with this module (and also too lazy to install it on Saturday), but could you tell me what is the reference field doing? If it's a node reference for nid, than it can be an int and we spare a few bytes.

Good question. The reference

ezra-g's picture

Good question. The reference is used by the smartqueue API to refer to the entity that the Smartqueue is about. For example: in a Taxonomy smartqueue, it is the queue's term ID. In a per-user queue, it is the UID. I'm not sure if there is a situation where it's non-numerical, so indeed it looks like this is one place where we can make a change. thanks!

Lucky shot =) Let's stick

Coornail's picture

Lucky shot =)
Let's stick with the numeric-nazi thing: owner is uid? (it should be I think)

High performance

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds: