11th February, 2016 Reprocess sitemap and topic merge SQLs
Login to Notey webserver
ssh ec2@52.23.181.117
Server log 52.23.181.117
Topic merge API
http://www.notey.com/api/topic/admin/merge/p.json?fromTopicIds=3984421&toTopicId=2723467 Topic disable API
http://www.notey.com/api2/admin/topic/disable?topicId=xxx
Topic titles with space in front or at the end ~954
select * from topic where (title like ' %') or (title like '% ');
Topics with most followers
select a.topic_id, b.title, count(*) as followers from user_topic_affinity a join topic b on a.topic_id =b.topic_id group by a.topic_id order by count(*) desc limit 500;
Top view posts
select cgroup_id, count(*) from user_comment_view group by cgroup_id order by count(*) desc limit 200;
Topics with 5 or less articles
SELECT topic_id, title, LENGTH(title) - LENGTH(REPLACE(title, ' ', ''))+1 as wordcount, articles FROM topic where (articles >0 and articles <=5) limit 10000000;
Topic titles with 4 or more words and with 5 or less articles
select * from (SELECT topic_id, title, LENGTH(title) - LENGTH(REPLACE(title, ' ', ''))+1 as wordcount, articles FROM topic where (articles >0 and articles <=5)) a where a.wordcount >=4 limit 10000000;
Topic titles with 1 articles that title contains 'burger'
SELECT topic_id, create_date, title, LENGTH(title) - LENGTH(REPLACE(title, ' ', ''))+1 as wordcount, articles FROM topic where (articles =1) and create_date <='2015-11-01' and title like '%burger%' limit 1000000;
Comments / articles belonging to a particular topic
select * from topic_comments_history where topic_id = 5172660;
Reprocess sitemap
ssh ec2@52.23.181.117
Server log 52.23.181.117
Topic merge API
http://www.notey.com/api/topic/admin/merge/p.json?fromTopicIds=3984421&toTopicId=2723467 Topic disable API
http://www.notey.com/api2/admin/topic/disable?topicId=xxx
Topic titles with space in front or at the end ~954
select * from topic where (title like ' %') or (title like '% ');
Topics with most followers
select a.topic_id, b.title, count(*) as followers from user_topic_affinity a join topic b on a.topic_id =b.topic_id group by a.topic_id order by count(*) desc limit 500;
Top view posts
select cgroup_id, count(*) from user_comment_view group by cgroup_id order by count(*) desc limit 200;
Topics with 5 or less articles
SELECT topic_id, title, LENGTH(title) - LENGTH(REPLACE(title, ' ', ''))+1 as wordcount, articles FROM topic where (articles >0 and articles <=5) limit 10000000;
Topic titles with 4 or more words and with 5 or less articles
select * from (SELECT topic_id, title, LENGTH(title) - LENGTH(REPLACE(title, ' ', ''))+1 as wordcount, articles FROM topic where (articles >0 and articles <=5)) a where a.wordcount >=4 limit 10000000;
Topic titles with 1 articles that title contains 'burger'
SELECT topic_id, create_date, title, LENGTH(title) - LENGTH(REPLACE(title, ' ', ''))+1 as wordcount, articles FROM topic where (articles =1) and create_date <='2015-11-01' and title like '%burger%' limit 1000000;
Comments / articles belonging to a particular topic
select * from topic_comments_history where topic_id = 5172660;
Reprocess sitemap
- Login to backend production server
- ssh ec2-user@54.84.50.126 be-asg(IP)
- sudo su
- cd /home/jetty/webapps/batch/WEB-INF/classes/config/
- Update generation sitemap conf file vi frm_quartz_jobs.xml
- service jetty restart
- tail -f /opt/jetty/logs/app/batch.log
Comments