Neo4j - 不知道如何改进密码查询

I have this query returning very fast, 0.5 seconds and returning all 303 records expected. Note: "Woka" here means "Book".

我有这个查询返回非常快,0.5秒并返回所有303个预期的记录。注意:“Woka”在这里的意思是“书”。

MATCH (p:Publisher)-[r:PUBLISHED]->(w:Woka)<-[s:AUTHORED]-(a:Author),
(l:Language)-[t:USED]->(w:Woka)-[u:INCLUDED]->(b:Bisac) 
WHERE (a.author_name = 'Camus, Albert') 
RETURN w.woka_id as woka_id, p.publisher_name as publisher_name, w.woka_title as woka_title, a.author_name as author_name, l.language_name as language_name, b.bisac_code as bisac_code, b.bisac_value as bisac_value 
ORDER BY woka_id;

And I want to add more info, a description for example. I have the Description nodes created and the relationships created, were exists, between Language and Description and Description and Book (Woka). The query below returns all descriptions as null, but only for 60 records instead of 303. This is because not all the books have a description. Execution time is still ok, 0.3 seconds.

我想添加更多信息,例如说明。我在语言和描述以及描述和书籍(Woka)之间创建了描述节点并且创建了关系。下面的查询将所有描述都返回为null,但仅返回60个记录而不是303.这是因为并非所有书籍都有描述。执行时间仍然可以,0.3秒。

MATCH (p:Publisher)-[r:PUBLISHED]->(w:Woka)<-[s:AUTHORED]-(a:Author), 
(l:Language)-[t:USED]->(w:Woka), (b:Bisac)<-[u:INCLUDED]-(w:Woka),
(d:Description)-[v:HAS_DESCRIPTION]-(w) 
WHERE (a.author_name = 'Camus, Albert') 
RETURN w.woka_id as woka_id, p.publisher_name as publisher_name, w.woka_title as woka_title, a.author_name as author_name, l.language_name as language_name, b.bisac_code as bisac_code, b.bisac_value as bisac_value, d.description as description 
ORDER BY woka_id;

However I know that some of the records left out from the result set, the difference between 50 and 303 does have a description. I build another query using OPTIONAL, but this one (shown below) never returns, runs for ever.

但是我知道从结果集中遗漏了一些记录,50和303之间的差异确实有描述。我使用OPTIONAL构建另一个查询,但是这个(如下所示)永远不会返回,永远运行。

MATCH (p:Publisher)-[r:PUBLISHED]->(w:Woka)<-[s:AUTHORED]-(a:Author),
 (l:Language)-[t:USED]->(w:Woka)-[u:INCLUDED]->(b:Bisac) 
OPTIONAL MATCH (d:Description)-[v:HAS_DESCRIPTION]-(w:Woka)-[:AUTHORED]-(a:Author)  
WHERE (a.author_name = 'Camus, Albert') 
RETURN w.woka_id as woka_id, p.publisher_name as publisher_name, w.woka_title as woka_title, a.author_name as author_name, l.language_name as language_name, b.bisac_code as bisac_code, b.bisac_value as bisac_value, d.description as description 
ORDER BY woka_id;

Don't know how to improve the query to get optional descriptions where exists and nulls when these don't exists for the original result set of 303 records?

不知道如何改进查询以获取存在的可选描述,并且当303记录的原始结果集不存在时为空?

3 个解决方案

#1

Could you try this?

你能试试吗?

MATCH (p:Publisher)-[r:PUBLISHED]->(w:Woka)<-[s:AUTHORED]-(a:Author), (l:Language)-[t:USED]->(w)-[u:INCLUDED]->(b:Bisac) 
WHERE (a.author_name = 'Camus, Albert') 
WITH p,r,w,s,a,l,t,u,b
OPTIONAL MATCH (d:Description)-[v:HAS_DESCRIPTION]-(w)
RETURN w.woka_id as woka_id, p.publisher_name as publisher_name, w.woka_title as woka_title, a.author_name as author_name, l.language_name as language_name, b.bisac_code as bisac_code, b.bisac_value as bisac_value, d.description as description 
ORDER BY woka_id;

#2

I think we already had this conversation some time ago.

我想我们前一段时间已经有了这次谈话。

you have to get down your intermediate cardinalities

你必须降低你的中间基数

use directions in your relationships

在你的人际关系中使用指示

don't repeat patterns you already solved, like

不要重复你已经解决的模式,比如

OPTIONAL MATCH (d:Description)-[v:HAS_DESCRIPTION]-(w:Woka)-[:AUTHORED]-(a:Author)

should be

OPTIONAL MATCH (d:Description)-[v:HAS_DESCRIPTION]-(w)

If you are matching long paths you create a lot of potential matches in between, for each of those rows the next match(es) are executed and if they create multiple rows each you get a multiplication of rows1*rows2*rows3

如果您匹配长路径,则会在两者之间创建大量潜在匹配,对于每个行执行下一个匹配,如果它们分别创建多个行,则会得到rows1 * rows2 * rows3的乘法

So you have to use either DISTINCT or an aggregation in between to get the cardinalities down as much as possible.

因此,您必须使用DISTINCT或其间的聚合来尽可能地降低基数。

Just demonstrating it for your first example, once with DISTINCT, once with collect. It might not be necessary here, but it is just for demonstration as the example is small enough.

只是为了你的第一个例子展示它,一次使用DISTINCT,一次使用collect。这里可能没有必要,但它仅用于演示,因为示例足够小。

MATCH (p:Publisher)-[r:PUBLISHED]->(w:Woka)<-[s:AUTHORED]-(a:Author)
WHERE (a.author_name = 'Camus, Albert') 
WITH DISTINCT w,a,p

MATCH (l:Language)-[t:USED]->(w)
WITH w,a,p, collect(l) as languages

MATCH (w)-[u:INCLUDED]->(b:Bisac) 
RETURN w.woka_id as woka_id, w.woka_title as woka_title, 
       p.publisher_name as publisher_name, 
       a.author_name as author_name, 
       [l in languages | l.language_name] as language_names, 
       b.bisac_code as bisac_code, b.bisac_value as bisac_value 
ORDER BY woka_id;

You correctly use OPTIONAL MATCH but there again you have to take into consideration that potential additional row-counts are multiplicated in.

你正确地使用了OPTIONAL MATCH但是你必须再次考虑到潜在的额外行数是多重的。

An alternative option for OPTIONAL MATCH is to use a path expression and deconstruction, e.g. for description:

OPTIONAL MATCH的另一种选择是使用路径表达式和解构,例如,描述:

RETURN w.woka_id as woka_id, w.woka_title as woka_title, 
       [p in ()<-[:HAS_DESCRIPTION]-(w) | head(nodes(p)).description] as descriptions

#3

In addition to @pablosaraiva's reply make sure you have an index on :Author and property author_name:

除@ pablosaraiva的回复外,请确保您有索引:作者和财产author_name:

create index on :Author(author_name)

If this and pablo's reply don't help, please post the query plan of your query. Use explain <myquery> for this (assuming you're on >=2.2)

如果这和pablo的回复没有帮助,请发布您的查询的查询计划。为此使用explain (假设你在> = 2.2)

#1