I have a Cypher query that we use to fetch real-time user feeds. Users have friends and users follow others as well. The query returns correct results but it takes a relatively long time to return the results (11395 ms).
I've created indexes on following properties of nodes User(userId, IsActive, FeaturedUser) Feed(AccessScope)
I also tried to find out if we can create indexes on relationships but without any luck.
MATCH (u:User { SpFeaturedUser: true })<-[:CREATED_BY]-(fe)-[:FEED_TYPE]->(:ServiceType{Id: 13})
WITH fe
ORDER BY fe.UpdatedUTCDateTime DESC
SKIP 0
LIMIT 100
OPTIONAL MATCH (fe)-[:CREATED_BY]->(u:User)
OPTIONAL MATCH (fe)-[:FEED_TYPE]->(st:ServiceType)
OPTIONAL MATCH (fe)-[endrs:ENDORSED]->(p:Place)
OPTIONAL MATCH (p:Place)-[placeImage:RELATED_IMAGE]->(pImg:Image)
OPTIONAL MATCH(fe)< -[likes: LIKES] - ()
OPTIONAL MATCH(fe)< -[cmts: COMMENT_ON_FEED] - (c)
OPTIONAL MATCH (m:Mentions)-[:MENTIONED_IN]->(fe)
OPTIONAL MATCH (fe)-[:RELATED_IMAGE]->(img:Image)
OPTIONAL MATCH (fe)-[:RELATED_SERVICE_URL]->(su)
WITH {
Description: fe.Description,
DescriptionEncoded: fe.DescriptionEncoded,
EndorsementType: fe.PostType,
CreatedUTCDateTime: fe.CreatedDateTime,
UpdatedUTCDateTime: fe.UpdatedDateTime,
StartDate: fe.StartDate,
EndDate: fe.EndDate,
AccessScope: fe.AccessScope,
Rating: fe.Rating,
EndorsementId: fe.ServiceId,
ServiceTypeId: st.Id,
LikeCount: Count(distinct(likes)),
IsLiked: EXISTS((fe) < -[:LIKES] - (: User{ UserId: "4F97D90E-922C-4C44-8F68-8311C60D76D9"}) ),
CommentsCount: Count(distinct(cmts)),
LastActivity: {
en: fe.Activity,
da: fe.DanishActivity
},
User: {
UserId: u.UserId,
FirstName: u.FirstName,
FileName: u.FileName,
SocialMediaAttribution: {
}
},
Endorsement_Mentions: CASE WHEN m IS NOT NULL THEN Collect(distinct {
Name: m.Name,
Type: m.TagType,
PlaceHolder: m.Placeholder,
TagID: m.TagId
}) ELSE [] END ,
Endorsement_Image: CASE WHEN img IS NOT NULL THEN Collect(distinct {
ImageUrl: img.ImageUrl,
Width: img.Width,
Height: img.Height,
Extension: img.Extension,
CreatedUTCDateTime: img.CreatedUTCDateTime
}) ELSE [] END,
URLPreviews: CASE WHEN su IS NOT NULL THEN Collect(distinct {
Title : su.UrlTitle ,
Description : su.UrlDescription ,
ImageURL : su.PreviewImage ,
URL : su.Url ,
Width : su.Width ,
Height : su.Height ,
Extension : su.Extension ,
IsVideoUrl : su.IsVideoUrl ,
VideoStreamUrl : su.VideoStreamUrl,
VideoType : su.VideoType
}) ELSE [] END,
Object: {
ObjectId: p.ObjectId,
Name: p.ObjectName,
IsFollowed: EXISTS((fe)-[:ENDORSED]->()<-[:FOLLOW]-({UserId : "4F97D90E-922C-4C44-8F68-8311C60D76D9"})),
AverageRating: {
Count: p.ObjectAvgRating
},
EndorsementCount: Count(distinct(endrs)),
Object_Image: CASE WHEN pImg IS NOT NULL THEN
Collect(distinct {
ImageUrl: pImg.ImageUrl,
Width: pImg.Width,
Height: pImg.Height,
Extension: pImg.Extension,
CreatedUTCDateTime: pImg.CreatedUTCDateTime
}) ELSE [] END,
Category: {
CategoryId: p.CategoryId
},
Country: {
ShowBarometer: false
}
}
} as Feed
RETURN DISTINCT(Feed)
ORDER BY Feed.UpdatedUTCDateTime DESC
The expected result is that this query should run fast enough to return the results within 2-3 secs as other queries are already running.
OPTIONAL MATCHclauses? You do not seem to be using anything they might find. Also why do you generate emptySocialMediaAttributionmaps? – cybersam