Empire cms related search call optimization

In Empire, I have a new idea. Add a table to store the keywords of all articles and aggregate the IDs of related articles together, similar to a search aggregation effect.

The advantage of this is that you can call articles with specified keywords in the article, and you can put aside the related search aggregation function that Empire originally had!

Because the original related search aggregation function of Empire was too slow! (Using SQL fuzzy query, the data is more than 100,000. If the server is stretched a little, it will be uncomfortable!)

1. Create the corresponding keyword data table

CREATE TABLE `phome_keywords` ( `id` INT(10) NOT NULL AUTO_INCREMENT , `md5` CHAR(32) NOT NULL , `title` VARCHAR(100) NOT NULL , `keyid` TEXT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

2. Assign all article keywords to this table, create a PHP file, and write code

The first step: export all the keywords, I use the code... Actually exporting the database directly is the fastest

<?php
echo '<meta http-equiv="refresh" content="2"/>';        // 自动跳转
require('../e/class/connect.php');        //引入数据库配置文件和公共函数文件
require('../e/class/db_sql.php');        //引入数据库操作文件
$link=db_connect();                //连接MYSQL
$empire=new mysqlquery();        //声明数据库操作类

// 参数配置
$num = 10000;     //每次修改多少条数据
// 获取页数
$file = "ktabe.txt";
if(file_exists($file)){
    $page_num = file_get_contents($file);
}else{
    $page_num = 0;
}
$page_id = $page_num*$num;
$sql=$empire->query("select id,keyboard from {$dbtbpre}ecms_news where `keyboard` != '22' or `keyboard` != ''  limit {$page_id},{$num}"); 
//查询新闻表最新10条记录
while($r=$empire->fetch($sql))        //循环获取查询记录
{
if(empty($r['id'])){
    exit;
}
echo $r['id'].'<br>';
$array = explode(",",$r['keyboard']);
foreach ($array as $v) {
    file_put_contents("ktabe_cache.txt",$v."\n",FILE_APPEND);
}
$array = [];

}

$page_num++;
file_put_contents($file,$page_num);     //记录下次需要查询的页面

db_close();                        //关闭MYSQL链接
$empire=null;                        //注消操作类变量
?>

The second step is to import the organized keywords into the data table

<?php
// echo '<meta http-equiv="refresh" content="2"/>';        // 自动跳转
require('../e/class/connect.php');        //引入数据库配置文件和公共函数文件
require('../e/class/db_sql.php');        //引入数据库操作文件
$link=db_connect();                //连接MYSQL
$empire=new mysqlquery();        //声明数据库操作类

$file = "ktabe_cache.txt";
$content = file_get_contents($file);
$array = explode(PHP_EOL,$content);
// print_r($array);
$i=1;
foreach ($array as $k=>$v){
    $v = stripslashes($v);
    $sql.= "(null,'".md5($v)."', '{$v}', ''),";
    // echo $sql;
    if($i>=100){
        $sql = rtrim($sql,',');
        $empire->query("INSERT INTO `{$dbtbpre}keywords` (`id`,`md5`, `title`, `keyid`) VALUES $sql;");
        $sql = '';
        $i=1;
    }
    $i++;
}
$sql = rtrim($sql,',');
$empire->query("INSERT INTO `{$dbtbpre}keywords` (`id`,`md5`, `title`, `keyid`) VALUES $sql;");

db_close();                        //关闭MYSQL链接
$empire=null;                        //注消操作类变量
?>

The third step is to remove duplicates

<?php

require('../e/class/connect.php');        //引入数据库配置文件和公共函数文件
require('../e/class/db_sql.php');        //引入数据库操作文件
$link=db_connect();                //连接MYSQL
$empire=new mysqlquery();        //声明数据库操作类
$sql=$empire->query("SELECT id,md5, count( md5 ) FROM `phome_keywords` GROUP BY md5 HAVING count( md5 ) > 1 limit 10000");
if(empty($sql)){
    die;
}else{
    echo '<meta http-equiv="refresh" content="2"/>';
}
while($r=$empire->fetch($sql))        //循环获取查询记录
{
    $empire->query("DELETE FROM `phome_keywords` WHERE `id` = {$r['id']};\n");
    echo $r['id']."  ";
}

echo "两秒后进行第二次删除";

db_close();                        //关闭MYSQL链接
$empire=null;                        //注消操作类变量
?>

After the code is written, the article keywords in the news table can be divided into the keyword data table one by one after running!

SQL mainly used for deduplication:

SELECT id, md5, count( md5 ) FROM `phome_keywords` GROUP BY md5 HAVING count( md5 ) > 1

3. Aggregate and store all article IDs with keywords in the keyword table

Since the SQL like of the database is too slow and stuck, I use sphinx.

PHP aggregation code:

<?php
echo '<meta http-equiv="refresh" content="1"/>';
require('../e/class/connect.php');        //引入数据库配置文件和公共函数文件
require('../e/class/db_sql.php');        //引入数据库操作文件
require ( "sphinxapi.php" );            //引入sphinx api文件
//配置sphinx
$cl = new SphinxClient ();
$host = "127.0.0.1";
$port = 9312;
$index = "article";
$cl->SetServer ( $host, $port );
$cl->SetConnectTimeout ( 1 );
$cl->SetArrayResult ( true );
$cl->SetLimits(0, 10, 1000);
//翻页配置
$file = "search.txt";
$num = 1000;
if(file_exists($file)){
    $page_num = file_get_contents($file);
}else{
    $page_num = 0;
}
$page_id = $page_num*$num;

$link=db_connect();                //连接MYSQL
$empire=new mysqlquery();        //声明数据库操作类
$sql=$empire->query("SELECT id,title FROM `phome_keywords` ORDER BY `id` ASC  limit {$page_id},{$num}");
while($r=$empire->fetch($sql))        //循环获取查询记录
{
    $res = $cl->Query ( $r['title'], $index );
    // print_r($res);
    if($res['total'] > 2){
        foreach ($res['matches'] as $v1){
            $sql_data.= $v1['id'] . ",";
        }
        $sql_data =rtrim($sql_data,",");
        if(!empty($sql_data)){
            $empire->query("UPDATE `phome_keywords` SET `keyid` = '{$sql_data}' WHERE `phome_keywords`.`id` = {$r['id']};");
        }
    }
    echo "本次聚合id".$r['id']."<br>";
}
$page_num++;
file_put_contents($file,$page_num);
db_close();                        //关闭MYSQL链接
$empire=null;                        //注消操作类变量
?>

Done! If you call it now, it will be more relevant!