I have vocabulary and it contains more terms, so I want to implement purge logic on it. i.e. I want to delete all the terms which are not used for some duration say 1 week.


term1 was associated to node1 on Oct 1st term2 was associated to node1 on Sep 30 term1 was associated to node2 on Oct 5th

So now term 2 was not being used for last one week, so now I need to delete that term alone.

I have tried following query,

SELECT ti.tid, td.name, ti.nid
FROM taxonomy_index ti
JOIN taxonomy_term_data td ON ( td.tid = ti.tid ) 
WHERE td.vid =48

However this query gives me both term1 and term2

2 Answers 2


Actually the reason for that is that the ti.created column is "The Unix timestamp when the node was created." It does not track the time when the tag was applied to the node, rather the time the node was created.

So that will always have the same value for each row that describes the tag associated with any one node. Look at this screenshot of my taxonomy_index table that displays three different tags I just applied to my node at different times:

enter image description here

From what I can tell, if you wanted that functionality, you'd have to store that timestamp (when a node was actually tagged) in your custom module yourself and you'd have to query that value.

Anyway, hope this helps clarify the issues a little. :-)

  • Why node created timestamp was used for term index? any specific reasons? Commented Oct 9, 2012 at 7:36
  • 1
    I have no idea why that value is stored there actually. It seems rather redundant and not as helpful, considering that it's already stored in the node table, right? Commented Oct 9, 2012 at 13:51

You will need a custom module which will track term usage.

file my_module.install

* Implements hook_install()
function my_module_install() {
    $spec = array(
        'description' => 'Last used timestamp',
            'type' => 'int',
            'not null' => TRUE,
            'default' => 0
        db_add_field('taxonomy_index', 'used', $spec);
    return true;

file `my_module.module`

function my_module_node_update($node) {
    //read all fields created by taxonomy module
    $term_fields = array_intersect_key(field_info_instances('node', $node->type), field_read_fields(array('module' => 'taxonomy')));
    // array for storing added terms
    $diff_terms = array();
    foreach ($term_fields as $field_name => $field) {
        $new_terms = $old_terms = array();
        // detect language
        $field_langcode = field_language('node', $node, $field_name, LANGUAGE_NONE);
        // we need onli "tid" field
        foreach ($node->{$field_name}[$field_langcode] as $term) {
            $new_terms[] = $term['tid'];
        foreach ($node->original->{$field_name}[$field_langcode] as $term) {
            $old_terms[] = $term['tid'];

        $diff_terms += array_diff($new_terms, $old_terms);
    // update table - set current timestamp 
            ->fields(array('used' => time(),))
            ->condition('taxonomy_index.tid', $diff_terms, 'IN')

This will update all terms, used by nodes. In table "taxonomy_index" you'll get new field with last updated timestamp

After you can use hook_cron to delete "old" terms.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.