SELECT * FROM tags PROCEDURE ANALYSE()

EXPLAIN SELECT t2.dbid, t2.tag, COUNT(t2.dbid) * 20 AS match_count
FROM tags AS t1, tags AS t2
WHERE t1.dbid = '105318'
AND t2.tag = t1.tag
AND t1.dbid != t2.dbid
GROUP BY t2.dbid
ORDER BY match_count;

select t2.dbid, t2.tag, count(t2.dbid) as match_count from tags as t1, tags as t2 where t1.dbid = '105319' and t2.tag = t1.tag and t1.dbid != t2.dbid GROUP BY t2.dbid 1 ORDER BY match_count;

/opt/mysql/bin/mysql -u root -h mysql-server < q4.sql > ! out.txt

/opt/mysql/bin/mysql -u root -h mysql-server --local-infile=1 

LOAD DATA LOCAL INFILE "/dc/elp/lionref/data/master/loggy.txt" INTO TABLE citations; 

USE reference;

CREATE TEMPORARY TABLE fulltxt
SELECT surname,atitle,title,type,journal,pqid,volume, issn,issue,spage,year
FROM citations
WHERE volume > 0
AND issue > 0
AND spage > 0
AND issn != ''
AND pqid != ''
; 

SELECT fulltxt.pqid AS candidate_ft, citations.chid
FROM citations,fulltxt
WHERE citations.issn = fulltxt.issn
AND citations.volume = fulltxt.volume
AND citations.issue = fulltxt.issue
AND citations.year = fulltxt.year
AND citations.spage = fulltxt.spage
AND citations.pqid = ''
; 

USE reference;

SELECT c2.pqid AS candidate,c1.surname,c1.atitle,c1.title,c1.type,c1.journal,c1.pqid,c1.volume, c1.issn,c1.issue,c1.spage,c1.year
FROM citations AS c1, citations AS c2
WHERE c1.issn = c2.issn
AND c1.volume = c2.volume
AND c1.issue = c2.issue
AND c1.year = c2.year
AND c1.spage = c2.spage
AND c1.pqid != c2.pqid
AND c1.pqid = ''
;