I am trying to query a database. I already have a file that includes some primary keys out of this whole database. Now I want to filter these primary keys and get only those primary keys which also agree to "other condition". My primary keys are associated to abstracts in the database. The abstracts are full-text indexed. Now I want to consider the abstracts using the given primary keys, look for my "other condition(terms)" in those abstracts and if its present I want to pull their primary keys out(which is going to be same from the file). My "other condition" is another file with a list of terms. I want to get the abstracts that contain those terms within the given primary keys.
我正在尝试查询数据库。我已经有一个文件，其中包含整个数据库中的一些主键。现在我想过滤这些主键，只获得那些同意“其他条件”的主键。我的主键与数据库中的摘要相关联。摘要是全文索引的。现在我想考虑使用给定主键的摘要，在那些摘要中查找我的“其他条件(术语)”，如果它现在我想拉出它们的主键(从文件中将是相同的)。我的“其他条件”是另一个带有术语列表的文件。我想获得在给定主键中包含这些术语的摘要。
My full-text search is something like this:
我的全文搜索是这样的：
while(){
$PK = $_;
foreach $foo(@foo){
my $sth = $dbh->prepare(qq{
SELECT value
FROM value_table
WHERE MATCH (column_text_indexed) AGAINST (? IN BOOLEAN MODE)
}) AND primary_key=$PK;
$sth->execute(qq{+"$foo"});
}
}
where $PK is coming from the list of primary keys I already have. $foo will be the list of the terms (condition 2) I am looking for.
$ PK来自我已经拥有的主键列表。 $ foo将是我正在寻找的条款(条件2)的列表。
Normally, I can run this query number of $PK times number of $foo. But I learned something about optimization by sub querying where I won't be running my query # $PK times # $foo. That will get rid of inner loop but will still form combination of every $PK with every term in file 2 that is @foo. Something like as follows:
通常，我可以运行$ PK次数$ foo的查询号。但是我通过子查询了解了一些关于优化的内容，我将不会运行我的查询＃$ PK times＃$ foo。这将摆脱内部循环，但仍将形成每个$ PK与文件2中@foo的每个术语的组合。如下所示：
while(){
$PK = $_;
my $sth = $dbh->prepare(qq{
SELECT value
FROM value_table
WHERE MATCH (column_text_indexed) AGAINST (**SUB QUERYING HERE**)
}) AND primary_key=$PK;
$sth->execute(qq{+"$foo"});
}
Just I don't know how to do it. I may be wrong with the syntax. I want to know how to write code for full-text search as well as a subquery. I hope this will be efficient than querying directly the combinations. Any help is greatly appreciated.
只是我不知道该怎么做。我的语法可能有问题。我想知道如何为全文搜索和子查询编写代码。我希望这比直接查询组合更有效。任何帮助是极大的赞赏。
3 个解决方案
#1
1
I don't think you need to use a subquery. But you can still get rid of the inner loop by combining the match strings.
我认为你不需要使用子查询。但是你仍然可以通过组合匹配字符串来摆脱内部循环。
my $against = join ' ', map {qq/"$_"/} @foo;
while (my $PK = ) {
chomp $PK;
my $sth = $dbh->prepare(qq{
SELECT value
FROM value_table
WHERE primary_key = ?
# no '+' so it can match against at least one of the words in the list
AND MATCH (column_text_indexed) AGAINST (? IN BOOLEAN MODE)
});
$sth->execute($PK, $against);
Update
更新
I revised it and have completely removed the query from the loops.
我修改了它并完全从循环中删除了查询。
my @primary_keys;
while (my $PK = ) {
chomp $PK;
push @primary_keys, $PK;
}
my $PK_list = join ',', map {qq/'$_'/} @primary_keys;
my $against = join ' ', map {qq/"$_"/} @foo;
my $sth = $dbh->prepare(qq{
SELECT value
FROM value_table
# placeholders can only represent single scalar values so $PK_list can't be bound
WHERE primary_key IN ($PK_list)
# no '+' so it can match against at least one of the words in the list
AND MATCH (column_text_indexed) AGAINST (? IN BOOLEAN MODE)
});
$sth->execute($against);
# continue with fetching the rows
...;
#2
1
Your syntax looks dodgy. I think you meant:
你的语法看起来很狡猾。我想你的意思是：
while(){
$PK = $_;
foreach $foo (@foo){
my $sth = $dbh->prepare(qq{
SELECT value
FROM value_table
WHERE MATCH (column_text_indexed)
AGAINST (**SUB QUERYING HERE**)
AND primary_key=$PK }); # '})' after AND clause
$sth->execute(qq{ $foo });
}
}
But why not make $PK an additional argument in this case (and use best practices)? :
但是为什么不在这种情况下使$ PK成为另一个参数(并使用最佳实践)？ ：
while ( my $PK = ) {
chomp $PK; # Remove trailing newline
foreach my $foo ( @foo ) { # Lexical $foo
my $sth = $dbh->prepare( qq{
SELECT value
FROM value_table
WHERE MATCH (column_text_indexed)
AGAINST (**SUB QUERYING HERE**)
AND primary_key=? }); # Extra placeholder
$sth->execute( $foo, $PK );
}
}
#3
1
If you want efficiency I would recommend to use least database transactions and operations. So in this case I think the best option is to just get the abstract from the database based the primary key and then search for the terms in that abstract by doing simple string search in your pearl or any other standard language code. I am not very sure of the length of your list of terms. But if its possible you can save it in a standard data-structure like array or list. The same operation in database would definitely take lot more time. I am not that good with pearl syntax so I am writing the algorithm.
如果你想要效率，我建议使用最少的数据库事务和操作。所以在这种情况下，我认为最好的选择是从主数据库中获取摘要，然后通过在珍珠或任何其他标准语言代码中进行简单的字符串搜索来搜索该摘要中的术语。我不太确定您的条款清单的长度。但如果可能，您可以将其保存在标准数据结构(如数组或列表)中。数据库中的相同操作肯定会花费更多的时间。我对珍珠语法不太好，所以我正在编写算法。
for all the terms in PK get the abstract as a string variable :
对于PK中的所有术语，将抽象作为字符串变量获取：
for each term in array/list: find the term in string variable containing abstract. If found add the PK to a new file.
对于数组/列表中的每个术语：在包含abstract的字符串变量中查找该术语。如果找到，请将PK添加到新文件中。
continue with next pk.
继续下一个PK。
if not found then continue next term in array/list.
如果没有找到，则继续下一个数组/列表中的术语。
扫一扫
1185
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
