# Time: 140530 5:01:19
# User@Host: root[root] @ localhost []
# Query_time: 2.421042 Lock_time: 0.000033 Rows_sent: 664125 Rows_examined: 664125
SET timestamp=1401404479;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `qa_words`;
# Time: 140530 5:14:00
# User@Host: szh[szh] @ localhost []
# Query_time: 10.751738 Lock_time: 0.000408 Rows_sent: 50 Rows_examined: 54195
SET timestamp=1401405240;
SELECT qa_posts.postid, qa_posts.categoryid, qa_posts.type, LEFT(qa_posts.type, 1) AS basetype, INSTR(qa_posts.type, '_HIDDEN')>0 AS hidden, qa_posts.acount, qa_posts.selchildid, qa_posts.closedbyid, qa_posts.upvotes, qa_posts.downvotes, qa_posts.netvotes, qa_posts.views, qa_posts.hotness, qa_posts.flagcount, qa_posts.title, qa_posts.tags, UNIX_TIMESTAMP(qa_posts.created) AS created, qa_posts.name, qa_categories.title AS categoryname, qa_categories.backpath AS categorybackpath, CONCAT_WS(',', qa_posts.catidpath1, qa_posts.catidpath2, qa_posts.catidpath3, qa_posts.categoryid) AS categoryids, qa_posts.userid, qa_posts.cookieid, INET_NTOA(qa_posts.createip) AS createip, qa_userpoints.points, qa_users.flags, qa_users.level, qa_users.email AS email, qa_users.handle AS handle, BINARY qa_users.avatarblobid AS avatarblobid, qa_users.avatarwidth, qa_users.avatarheight, score, matchparts FROM qa_posts LEFT JOIN qa_categories ON qa_categories.categoryid=qa_posts.categoryid LEFT JOIN qa_users ON qa_posts.userid=qa_users.userid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid JOIN (SELECT questionid, SUM(score)+2*(LOG(10000)*(qa_posts.hotness-(SELECT MIN(hotness) FROM qa_posts WHERE type='Q'))/((SELECT MAX(hotness) FROM qa_posts WHERE type='Q')-(SELECT MIN(hotness) FROM qa_posts WHERE type='Q')))+LOG(questionid)/1000000 AS score, GROUP_CONCAT(CONCAT_WS(':', matchposttype, matchpostid, ROUND(score,3))) AS matchparts FROM ((SELECT postid AS questionid, LOG(10000/titlecount) AS score, 'Q' AS matchposttype, postid AS matchpostid FROM qa_titlewords JOIN qa_words ON qa_titlewords.wordid=qa_words.wordid WHERE word IN ('windows','7','мен','8') AND titlecount<10000) UNION ALL (SELECT questionid, (1-1/(1+count))*LOG(10000/contentcount)*(CASE qa_contentwords.type WHEN 'Q' THEN 1.0 WHEN 'A' THEN 0.5 ELSE 0.25 END) AS score, qa_contentwords.type AS matchposttype, qa_contentwords.postid AS matchpostid FROM qa_contentwords JOIN qa_words ON qa_contentwords.wordid=qa_words.wordid WHERE word IN ('windows','7','мен','8') AND contentcount<10000) UNION ALL (SELECT postid AS questionid, 2*LOG(10000/tagwordcount) AS score, 'Q' AS matchposttype, postid AS matchpostid FROM qa_tagwords JOIN qa_words ON qa_tagwords.wordid=qa_words.wordid WHERE word IN ('windows','7','мен','8') AND tagwordcount<10000) UNION ALL (SELECT postid AS questionid, LOG(10000/qposts) AS score, 'Q' AS matchposttype, postid AS matchpostid FROM qa_posts JOIN qa_users ON qa_posts.userid=qa_users.userid JOIN qa_userpoints ON qa_userpoints.userid=qa_users.userid WHERE handle IN ('windows','7','мен','8') AND type='Q') UNION ALL (SELECT postid AS questionid, LOG(10000/qposts) AS score, 'Q' AS matchposttype, postid AS matchpostid FROM qa_posts JOIN qa_users ON qa_posts.userid=qa_users.userid JOIN qa_userpoints ON qa_userpoints.userid=qa_users.userid WHERE handle='windows 7 мен 8' AND type='Q')) x LEFT JOIN qa_posts ON qa_posts.postid=questionid GROUP BY questionid ORDER BY score DESC LIMIT 1300,50) y ON qa_posts.postid=y.questionid;
# Time: 140530 5:20:18
# User@Host: szh[szh] @ localhost []
# Query_time: 1.349829 Lock_time: 0.000165 Rows_sent: 3 Rows_examined: 19
SET timestamp=1401405618;
SELECT qa_posts.postid, qa_posts.categoryid, qa_posts.type, LEFT(qa_posts.type, 1) AS basetype, INSTR(qa_posts.type, '_HIDDEN')>0 AS hidden, qa_posts.acount, qa_posts.selchildid, qa_posts.closedbyid, qa_posts.upvotes, qa_posts.downvotes, qa_posts.netvotes, qa_posts.views, qa_posts.hotness, qa_posts.flagcount, qa_posts.title, qa_posts.tags, UNIX_TIMESTAMP(qa_posts.created) AS created, qa_posts.name, qa_categories.title AS categoryname, qa_categories.backpath AS categorybackpath, CONCAT_WS(',', qa_posts.catidpath1, qa_posts.catidpath2, qa_posts.catidpath3, qa_posts.categoryid) AS categoryids, qa_posts.userid, qa_posts.cookieid, INET_NTOA(qa_posts.createip) AS createip, qa_userpoints.points, qa_users.flags, qa_users.level, qa_users.email AS email, qa_users.handle AS handle, BINARY qa_users.avatarblobid AS avatarblobid, qa_users.avatarwidth, qa_users.avatarheight FROM qa_posts LEFT JOIN qa_categories ON qa_categories.categoryid=qa_posts.categoryid LEFT JOIN qa_users ON qa_posts.userid=qa_users.userid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid JOIN (SELECT postid FROM qa_posttags WHERE wordid=(SELECT wordid FROM qa_words WHERE word='жолда жүру ережесі' AND word='жолда жүру ережесі' COLLATE utf8_bin LIMIT 1) ORDER BY postcreated DESC LIMIT 0,42) y ON qa_posts.postid=y.postid;
# Time: 140530 5:30:24
# User@Host: root[root] @ localhost []
# Query_time: 21.144146 Lock_time: 0.000102 Rows_sent: 1 Rows_examined: 43
use mysql;
SET timestamp=1401406224;
select ifnull(sum(data_length + index_length), 0) from information_schema.tables where table_schema = 'szh';
# Time: 140530 6:00:09
# User@Host: szh[szh] @ localhost []
# Query_time: 1.000660 Lock_time: 0.000219 Rows_sent: 0 Rows_examined: 4
use szh;
SET timestamp=1401408009;
UPDATE qa_posts AS x, (SELECT parents.postid, parents.created AS qcreated, COALESCE(MAX(children.created), parents.created) as acreated, COUNT(children.postid) AS acount, parents.netvotes, parents.views FROM qa_posts AS parents LEFT JOIN qa_posts AS children ON parents.postid=children.parentid AND children.type='A' WHERE parents.postid>=463263 AND parents.postid<=463263 AND LEFT(parents.type, 1)='Q' GROUP BY postid) AS a SET x.hotness=(((TO_DAYS(a.qcreated)-734138)*86400.0+TIME_TO_SEC(a.qcreated))*50 + ((TO_DAYS(a.acreated)-734138)*86400.0+TIME_TO_SEC(a.acreated))*50 + (a.acount+0.0)*8000000 + (a.netvotes+0.0)*8000000 + (a.views+0.0+1)*200000), x.views=x.views+1, x.lastviewip=INET_ATON('212.96.69.95') WHERE x.postid=a.postid;
