Wiki

Database

Update new content id for Gold-Standard (runtime Biblicious: few seconds)

CREATE TABLE tmp_gold_standard AS
SELECT lgs.content_id, lgs.simhash1
FROM log_gold_standard lgs;

INSERT INTO tmp_gold_standard
SELECT gs.content_id, gs.simhash1
FROM gold_standard gs;

UPDATE log_gold_standard lgs
JOIN (SELECT tgs2.content_id, tgs2.simhash1 
  FROM tmp_gold_standard tgs2
  ORDER BY content_id ASC) tgs
ON (tgs.simhash1  = lgs.new_simhash1 AND tgs.content_id > lgs.content_id)
SET lgs.new_content_id = tgs.content_id
WHERE lgs.new_content_id = 0;

DROP TABLE tmp_gold_standard;

Update current_content_id * Every second statement has to be iterated until no more changes occur * Goldstandard in seconds, 4 iterations * Bibtex in 7-9 sec per iteration (11 sec for first statement), 79 iterations * Bookmark in (24 sec for first statement), * To avoid having to type all iterations by hand use the following script

#!/bin/bash                                                                                                                                                                                                    

pw=PASSWORD

a=10
while [ $a -gt 0 ]; do
    a=`mysql -u USER -p$pw bibsonomy -e "STATEMENT; SELECT ROW_COUNT();" | head -n 4 | tail -n 1`;
    echo $a;
done

UPDATE log_bibtex lba 
LEFT JOIN log_bibtex lbb ON (lba.new_content_id = lbb.content_id)
SET lba.current_content_id = lba.new_content_id
WHERE lbb.content_id IS NULL AND lba.new_content_id != 0;

UPDATE log_bibtex lba 
JOIN log_bibtex lbb ON (lba.new_content_id = lbb.content_id)
SET lba.current_content_id = lbb.current_content_id
WHERE lbb.current_content_id != 0 AND lba.new_content_id != 0;
-- Until no more rows are changed

UPDATE log_bookmark lba 
LEFT JOIN log_bookmark lbb ON (lba.new_content_id = lbb.content_id)
SET lba.current_content_id = lba.new_content_id
WHERE lbb.content_id IS NULL AND lba.new_content_id != 0;

UPDATE log_bookmark lba 
JOIN log_bookmark lbb ON (lba.new_content_id = lbb.content_id)
SET lba.current_content_id = lbb.current_content_id
WHERE lbb.current_content_id != 0 AND lba.new_content_id != 0;
-- Until no more rows are changed

UPDATE log_gold_standard lga 
LEFT JOIN log_gold_standard lgb ON (lga.new_content_id = lgb.content_id)
SET lga.current_content_id = lga.new_content_id
WHERE lgb.content_id IS NULL AND lga.new_content_id != 0;

UPDATE log_gold_standard lga 
JOIN log_gold_standard lgb ON (lga.new_content_id = lgb.content_id)
SET lga.current_content_id = lgb.current_content_id
WHERE lgb.current_content_id != 0 AND lga.new_content_id != 0;
-- Until no more rows are changed

Add Indexes

Create index content_id_idx on log_tas (content_id);

TODO: Check for other indexes to be used on the log tables (compare biblicious)