Advanced query tuning example

This page describes advanced query optimizations based on native index capabilities.

One of the most important and useful ways of optimizing Cypher® queries involves creating appropriate indexes. This is described in more detail in Create, show, and drop indexes, and demonstrated in Basic query tuning example. In summary, an index will be based on the combination of a Label and a property. Any Cypher query that searches for nodes with a specific label and some predicate on the property (equality, range or existence) will be planned to use the index if the cost planner deems that to be the most efficient solution.

In order to benefit from enhancements provided by native indexes, it is useful to understand when index-backed property lookup and index-backed ORDER BY will come into play. Let’s explain how to use these features with a more advanced query tuning example.

If you are upgrading an existing store, it may be necessary to drop and re-create existing indexes. For information on native index support and upgrade considerations regarding indexes, see Operations Manual → Performance → Index configuration.

The data set

In this section, examples demonstrates the impact native indexes can have on query performance under certain conditions. You will use a movies dataset to illustrate this more advanced query tuning.

In this tutorial, you import data from the following CSV files:

  • movies.csv

  • actors.csv

  • directors.csv

Movies

The movies.csv file contains two columns title, released and tagline.

The content of the movies.csv file:

movies.csv
title,released,tagline Something's Gotta Give,1975,null Johnny Mnemonic,1995,The hottest data on earth. In the coolest head in town The Replacements,2000,"Pain heals, Chicks dig scars... Glory lasts forever" The Devil's Advocate,1997,Evil has its winning ways The Matrix Revolutions,2003,Everything that has a beginning has an end The Matrix Reloaded,2003,Free your mind The Matrix,1999,Welcome to the Real World The Matrix Revolutions,2003,Everything that has a beginning has an end The Matrix Reloaded,2003,Free your mind The Matrix,1999,Welcome to the Real World The Matrix Revolutions,2003,Everything that has a beginning has an end The Matrix Reloaded,2003,Free your mind The Matrix,1999,Welcome to the Real World V for Vendetta,2006,Freedom! Forever! Cloud Atlas,2012,Everything is connected The Matrix Revolutions,2003,Everything that has a beginning has an end The Matrix Reloaded,2003,Free your mind The Matrix,1999,Welcome to the Real World Speed Racer,2008,Speed has no limits Cloud Atlas,2012,Everything is connected The Matrix Revolutions,2003,Everything that has a beginning has an end The Matrix Reloaded,2003,Free your mind The Matrix,1999,Welcome to the Real World Ninja Assassin,2009,Prepare to enter a secret world of assassins V for Vendetta,2006,Freedom! Forever! Speed Racer,2008,Speed has no limits V for Vendetta,2006,Freedom! Forever! Speed Racer,2008,Speed has no limits Cloud Atlas,2012,Everything is connected The Matrix Revolutions,2003,Everything that has a beginning has an end The Matrix Reloaded,2003,Free your mind The Matrix,1999,Welcome to the Real World Ninja Assassin,2009,Prepare to enter a secret world of assassins V for Vendetta,2006,Freedom! Forever! Speed Racer,2008,Speed has no limits V for Vendetta,2006,Freedom! Forever! Ninja Assassin,2009,Prepare to enter a secret world of assassins Speed Racer,2008,Speed has no limits V for Vendetta,2006,Freedom! Forever! The Matrix Revolutions,2003,Everything that has a beginning has an end The Matrix Reloaded,2003,Free your mind The Matrix,1999,Welcome to the Real World The Matrix,1999,Welcome to the Real World That Thing You Do,1996,In every life there comes a time when that thing you dream becomes that thing you do The Devil's Advocate,1997,Evil has its winning ways The Devil's Advocate,1997,Evil has its winning ways The Devil's Advocate,1997,Evil has its winning ways Jerry Maguire,2000,The rest of his life begins now. Top Gun,1986,"I feel the need, the need for speed." A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." Something's Gotta Give,1975,null One Flew Over the Cuckoo's Nest,1975,"If he's crazy, what does that make you?" Hoffa,1992,He didn't want law. He wanted justice. As Good as It Gets,1997,A comedy from the heart that goes for the throat. A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." Apollo 13,1995,"Houston, we have a problem." Frost/Nixon,2008,400 million people were waiting for the truth. A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." Stand By Me,1995,"For some, it's the last real taste of innocence, and the first real taste of life. But for everyone, it's the time that memories are made of." A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." What Dreams May Come,1998,After life there is more. The end is just the beginning. As Good as It Gets,1997,A comedy from the heart that goes for the throat. Jerry Maguire,2000,The rest of his life begins now. A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." Hoffa,1992,He didn't want law. He wanted justice. A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." Ninja Assassin,2009,Prepare to enter a secret world of assassins V for Vendetta,2006,Freedom! Forever! A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." When Harry Met Sally,1998,At odds in life... in love on-line. Stand By Me,1995,"For some, it's the last real taste of innocence, and the first real taste of life. But for everyone, it's the time that memories are made of." A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." When Harry Met Sally,1998,At odds in life... in love on-line. A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." A Few Good Men,1992,"In the heart of the nation's capital, in a courthouse of the U.S. government, one man will stop at nothing to keep his honor, and one will stop at nothing to find the truth." Top Gun,1986,"I feel the need, the need for speed." Top Gun,1986,"I feel the need, the need for speed." Top Gun,1986,"I feel the need, the need for speed." Top Gun,1986,"I feel the need, the need for speed." When Harry Met Sally,1998,At odds in life... in love on-line. Joe Versus the Volcano,1990,"A story of love, lava and burning desire." Sleepless in Seattle,1993,"What if someone you never met, someone you never saw, someone you never knew was the only someone for you?" You've Got Mail,1998,At odds in life... in love on-line. Top Gun,1986,"I feel the need, the need for speed." Top Gun,1986,"I feel the need, the need for speed." Top Gun,1986,"I feel the need, the need for speed." Jerry Maguire,2000,The rest of his life begins now. Jerry Maguire,2000,The rest of his life begins now. Stand By Me,1995,"For some, it's the last real taste of innocence, and the first real taste of life. But for everyone, it's the time that memories are made of." Jerry Maguire,2000,The rest of his life begins now. Jerry Maguire,2000,The rest of his life begins now. The Green Mile,1999,Walk a mile you'll never forget. Jerry Maguire,2000,The rest of his life begins now. Jerry Maguire,2000,The rest of his life begins now. Jerry Maguire,2000,The rest of his life begins now. Jerry Maguire,2000,The rest of his life begins now. Jerry Maguire,2000,The rest of his life begins now. Jerry Maguire,2000,The rest of his life begins now. Stand By Me,1995,"For some, it's the last real taste of innocence, and the first real taste of life. But for everyone, it's the time that memories are made of." Stand By Me,1995,"For some, it's the last real taste of innocence, and the first real taste of life. But for everyone, it's the time that memories are made of." Stand By Me,1995,"For some, it's the last real taste of innocence, and the first real taste of life. But for everyone, it's the time that memories are made of." Stand By Me,1995,"For some, it's the last real taste of innocence, and the first real taste of life. But for everyone, it's the time that memories are made of." RescueDawn,2006,Based on the extraordinary true story of one man's fight for freedom Stand By Me,1995,"For some, it's the last real taste of innocence, and the first real taste of life. But for everyone, it's the time that memories are made of." Cast Away,2000,"At the edge of the world, his journey begins." Twister,1996,Don't Breathe. Don't Look Back. As Good as It Gets,1997,A comedy from the heart that goes for the throat. You've Got Mail,1998,At odds in life... in love on-line. As Good as It Gets,1997,A comedy from the heart that goes for the throat. As Good as It Gets,1997,A comedy from the heart that goes for the throat. What Dreams May Come,1998,After life there is more. The end is just the beginning. Snow Falling on Cedars,1999,First loves last. Forever. What Dreams May Come,1998,After life there is more. The end is just the beginning. What Dreams May Come,1998,After life there is more. The end is just the beginning. RescueDawn,2006,Based on the extraordinary true story of one man's fight for freedom Bicentennial Man,1999,One robot's 200 year journey to become an ordinary man. The Birdcage,1996,Come as you are What Dreams May Come,1998,After life there is more. The end is just the beginning. What Dreams May Come,1998,After life there is more. The end is just the beginning. Snow Falling on Cedars,1999,First loves last. Forever. Ninja Assassin,2009,Prepare to enter a secret world of assassins Snow Falling on Cedars,1999,First loves last. Forever. The Green Mile,1999,Walk a mile you'll never forget. Snow Falling on Cedars,1999,First loves last. Forever. Snow Falling on Cedars,1999,First loves last. Forever. You've Got Mail,1998,At odds in life... in love on-line. You've Got Mail,1998,At odds in life... in love on-line. RescueDawn,2006,Based on the extraordinary true story of one man's fight for freedom You've Got Mail,1998,At odds in life... in love on-line. A League of Their Own,1992,Once in a lifetime you get a chance to do something different. The Polar Express,2004,This Holiday Season… Believe Charlie Wilson's War,2007,A stiff drink. A little mascara. A lot of nerve. Who said they couldn't bring down the Soviet empire. Cast Away,2000,"At the edge of the world, his journey begins." Apollo 13,1995,"Houston, we have a problem." The Green Mile,1999,Walk a mile you'll never forget. The Da Vinci Code,2006,Break The Codes Cloud Atlas,2012,Everything is connected That Thing You Do,1996,In every life there comes a time when that thing you dream becomes that thing you do Joe Versus the Volcano,1990,"A story of love, lava and burning desire." Sleepless in Seattle,1993,"What if someone you never met, someone you never saw, someone you never knew was the only someone for you?" You've Got Mail,1998,At odds in life... in love on-line. That Thing You Do,1996,In every life there comes a time when that thing you dream becomes that thing you do Sleepless in Seattle,1993,"What if someone you never met, someone you never saw, someone you never knew was the only someone for you?" You've Got Mail,1998,At odds in life... in love on-line. When Harry Met Sally,1998,At odds in life... in love on-line. When Harry Met Sally,1998,At odds in life... in love on-line. Sleepless in Seattle,1993,"What if someone you never met, someone you never saw, someone you never knew was the only someone for you?" Sleepless in Seattle,1993,"What if someone you never met, someone you never saw, someone you never knew was the only someone for you?" Sleepless in Seattle,1993,"What if someone you never met, someone you never saw, someone you never knew was the only someone for you?" A League of Their Own,1992,Once in a lifetime you get a chance to do something different. Sleepless in Seattle,1993,"What if someone you never met, someone you never saw, someone you never knew was the only someone for you?" Joe Versus the Volcano,1990,"A story of love, lava and burning desire." The Birdcage,1996,Come as you are Joe Versus the Volcano,1990,"A story of love, lava and burning desire." When Harry Met Sally,1998,At odds in life... in love on-line. When Harry Met Sally,1998,At odds in life... in love on-line. When Harry Met Sally,1998,At odds in life... in love on-line. That Thing You Do,1996,In every life there comes a time when that thing you dream becomes that thing you do The Replacements,2000,"Pain heals, Chicks dig scars... Glory lasts forever" Unforgiven,1992,"It's a hell of a thing, killing a man" The Birdcage,1996,Come as you are The Replacements,2000,"Pain heals, Chicks dig scars... Glory lasts forever" The Replacements,2000,"Pain heals, Chicks dig scars... Glory lasts forever" The Replacements,2000,"Pain heals, Chicks dig scars... Glory lasts forever" RescueDawn,2006,Based on the extraordinary true story of one man's fight for freedom Twister,1996,Don't Breathe. Don't Look Back. RescueDawn,2006,Based on the extraordinary true story of one man's fight for freedom Charlie Wilson's War,2007,A stiff drink. A little mascara. A lot of nerve. Who said they couldn't bring down the Soviet empire. The Birdcage,1996,Come as you are Unforgiven,1992,"It's a hell of a thing, killing a man" Unforgiven,1992,"It's a hell of a thing, killing a man" Unforgiven,1992,"It's a hell of a thing, killing a man" Johnny Mnemonic,1995,The hottest data on earth. In the coolest head in town Johnny Mnemonic,1995,The hottest data on earth. In the coolest head in town Johnny Mnemonic,1995,The hottest data on earth. In the coolest head in town Johnny Mnemonic,1995,The hottest data on earth. In the coolest head in town Cloud Atlas,2012,Everything is connected Cloud Atlas,2012,Everything is connected Cloud Atlas,2012,Everything is connected The Da Vinci Code,2006,Break The Codes The Da Vinci Code,2006,Break The Codes The Da Vinci Code,2006,Break The Codes Apollo 13,1995,"Houston, we have a problem." Frost/Nixon,2008,400 million people were waiting for the truth. The Da Vinci Code,2006,Break The Codes V for Vendetta,2006,Freedom! Forever! V for Vendetta,2006,Freedom! Forever! V for Vendetta,2006,Freedom! Forever! Ninja Assassin,2009,Prepare to enter a secret world of assassins Speed Racer,2008,Speed has no limits V for Vendetta,2006,Freedom! Forever! Speed Racer,2008,Speed has no limits Speed Racer,2008,Speed has no limits Speed Racer,2008,Speed has no limits Speed Racer,2008,Speed has no limits Speed Racer,2008,Speed has no limits Ninja Assassin,2009,Prepare to enter a secret world of assassins Speed Racer,2008,Speed has no limits Ninja Assassin,2009,Prepare to enter a secret world of assassins The Green Mile,1999,Walk a mile you'll never forget. The Green Mile,1999,Walk a mile you'll never forget. Frost/Nixon,2008,400 million people were waiting for the truth. The Green Mile,1999,Walk a mile you'll never forget. Apollo 13,1995,"Houston, we have a problem." The Green Mile,1999,Walk a mile you'll never forget. The Green Mile,1999,Walk a mile you'll never forget. The Green Mile,1999,Walk a mile you'll never forget. Frost/Nixon,2008,400 million people were waiting for the truth. Frost/Nixon,2008,400 million people were waiting for the truth. Bicentennial Man,1999,One robot's 200 year journey to become an ordinary man. Frost/Nixon,2008,400 million people were waiting for the truth. One Flew Over the Cuckoo's Nest,1975,"If he's crazy, what does that make you?" Hoffa,1992,He didn't want law. He wanted justice. Hoffa,1992,He didn't want law. He wanted justice. Hoffa,1992,He didn't want law. He wanted justice. Apollo 13,1995,"Houston, we have a problem." A League of Their Own,1992,Once in a lifetime you get a chance to do something different. Twister,1996,Don't Breathe. Don't Look Back. Apollo 13,1995,"Houston, we have a problem." Charlie Wilson's War,2007,A stiff drink. A little mascara. A lot of nerve. Who said they couldn't bring down the Soviet empire. Twister,1996,Don't Breathe. Don't Look Back. Twister,1996,Don't Breathe. Don't Look Back. The Polar Express,2004,This Holiday Season… Believe Cast Away,2000,"At the edge of the world, his journey begins." One Flew Over the Cuckoo's Nest,1975,"If he's crazy, what does that make you?" Something's Gotta Give,1975,null Something's Gotta Give,1975,null Something's Gotta Give,1975,null Something's Gotta Give,1975,null Bicentennial Man,1999,One robot's 200 year journey to become an ordinary man. Charlie Wilson's War,2007,A stiff drink. A little mascara. A lot of nerve. Who said they couldn't bring down the Soviet empire. A League of Their Own,1992,Once in a lifetime you get a chance to do something different. A League of Their Own,1992,Once in a lifetime you get a chance to do something different. A League of Their Own,1992,Once in a lifetime you get a chance to do something different. A League of Their Own,1992,Once in a lifetime you get a chance to do something different. The Replacements,2000,"Pain heals, Chicks dig scars... Glory lasts forever" The Da Vinci Code,2006,Break The Codes The Birdcage,1996,Come as you are Unforgiven,1992,"It's a hell of a thing, killing a man" The Replacements,2000,"Pain heals, Chicks dig scars... Glory lasts forever" Cloud Atlas,2012,Everything is connected The Da Vinci Code,2006,Break The Codes The Replacements,2000,"Pain heals, Chicks dig scars... Glory lasts forever"

Actors

The actors.csv file contains two columns title, roles, name, and born.

The content of the actors.csv file:

actors.csv
title,roles,name,born Something's Gotta Give,Julian Mercer,Keanu Reeves,1964 Johnny Mnemonic,Johnny Mnemonic,Keanu Reeves,1964 The Replacements,Shane Falco,Keanu Reeves,1964 The Devil's Advocate,Kevin Lomax,Keanu Reeves,1964 The Matrix Revolutions,Neo,Keanu Reeves,1964 The Matrix Reloaded,Neo,Keanu Reeves,1964 The Matrix,Neo,Keanu Reeves,1964 The Matrix Revolutions,Trinity,Carrie-Anne Moss,1967 The Matrix Reloaded,Trinity,Carrie-Anne Moss,1967 The Matrix,Trinity,Carrie-Anne Moss,1967 The Matrix Revolutions,Morpheus,Laurence Fishburne,1961 The Matrix Reloaded,Morpheus,Laurence Fishburne,1961 The Matrix,Morpheus,Laurence Fishburne,1961 V for Vendetta,V,Hugo Weaving,1960 Cloud Atlas,Bill Smoke;Haskell Moore;Tadeusz Kesselring;Nurse Noakes;Boardman Mephi;Old Georgie,Hugo Weaving,1960 The Matrix Revolutions,Agent Smith,Hugo Weaving,1960 The Matrix Reloaded,Agent Smith,Hugo Weaving,1960 The Matrix,Agent Smith,Hugo Weaving,1960 The Matrix,Emil,Emil Eifrem,1978 That Thing You Do,Tina,Charlize Theron,1975 The Devil's Advocate,Mary Ann Lomax,Charlize Theron,1975 The Devil's Advocate,John Milton,Al Pacino,1940 Jerry Maguire,Jerry Maguire,Tom Cruise,1962 Top Gun,Maverick,Tom Cruise,1962 A Few Good Men,Lt. Daniel Kaffee,Tom Cruise,1962 Something's Gotta Give,Harry Sanborn,Jack Nicholson,1937 One Flew Over the Cuckoo's Nest,Randle McMurphy,Jack Nicholson,1937 Hoffa,Hoffa,Jack Nicholson,1937 As Good as It Gets,Melvin Udall,Jack Nicholson,1937 A Few Good Men,Col. Nathan R. Jessup,Jack Nicholson,1937 A Few Good Men,Lt. Cdr. JoAnne Galloway,Demi Moore,1962 Apollo 13,Jack Swigert,Kevin Bacon,1958 Frost/Nixon,Jack Brennan,Kevin Bacon,1958 A Few Good Men,Capt. Jack Ross,Kevin Bacon,1958 Stand By Me,Ace Merrill,Kiefer Sutherland,1966 A Few Good Men,Lt. Jonathan Kendrick,Kiefer Sutherland,1966 A Few Good Men,Cpl. Jeffrey Barnes,Noah Wyle,1971 What Dreams May Come,Albert Lewis,Cuba Gooding Jr.,1968 As Good as It Gets,Frank Sachs,Cuba Gooding Jr.,1968 Jerry Maguire,Rod Tidwell,Cuba Gooding Jr.,1968 A Few Good Men,Cpl. Carl Hammaker,Cuba Gooding Jr.,1968 A Few Good Men,Lt. Sam Weinberg,Kevin Pollak,1957 Hoffa,Frank Fitzsimmons,J.T. Walsh,1943 A Few Good Men,Lt. Col. Matthew Andrew Markinson,J.T. Walsh,1943 A Few Good Men,Pfc. Louden Downey,James Marshall,1967 A Few Good Men,Dr. Stone,Christopher Guest,1948 A Few Good Men,Man in Bar,Aaron Sorkin,1961 Top Gun,Charlie,Kelly McGillis,1957 Top Gun,Iceman,Val Kilmer,1959 Top Gun,Goose,Anthony Edwards,1962 Top Gun,Viper,Tom Skerritt,1933 When Harry Met Sally,Sally Albright,Meg Ryan,1961 Joe Versus the Volcano,DeDe;Angelica Graynamore;Patricia Graynamore,Meg Ryan,1961 Sleepless in Seattle,Annie Reed,Meg Ryan,1961 You've Got Mail,Kathleen Kelly,Meg Ryan,1961 Top Gun,Carole,Meg Ryan,1961 Jerry Maguire,Dorothy Boyd,Renee Zellweger,1969 Jerry Maguire,Avery Bishop,Kelly Preston,1962 Stand By Me,Vern Tessio,Jerry O'Connell,1974 Jerry Maguire,Frank Cushman,Jerry O'Connell,1974 Jerry Maguire,Bob Sugar,Jay Mohr,1970 The Green Mile,Jan Edgecomb,Bonnie Hunt,1961 Jerry Maguire,Laurel Boyd,Bonnie Hunt,1961 Jerry Maguire,Marcee Tidwell,Regina King,1971 Jerry Maguire,Ray Boyd,Jonathan Lipnicki,1990 Stand By Me,Chris Chambers,River Phoenix,1970 Stand By Me,Teddy Duchamp,Corey Feldman,1971 Stand By Me,Gordie Lachance,Wil Wheaton,1972 Stand By Me,Denny Lachance,John Cusack,1966 RescueDawn,Admiral,Marshall Bell,1942 Stand By Me,Mr. Lachance,Marshall Bell,1942 Cast Away,Kelly Frears,Helen Hunt,1963 Twister,Dr. Jo Harding,Helen Hunt,1963 As Good as It Gets,Carol Connelly,Helen Hunt,1963 You've Got Mail,Frank Navasky,Greg Kinnear,1963 As Good as It Gets,Simon Bishop,Greg Kinnear,1963 What Dreams May Come,Simon Bishop,Annabella Sciorra,1960 Snow Falling on Cedars,Nels Gudmundsson,Max von Sydow,1929 What Dreams May Come,The Tracker,Max von Sydow,1929 What Dreams May Come,The Face,Werner Herzog,1942 Bicentennial Man,Andrew Marin,Robin Williams,1951 The Birdcage,Armand Goldman,Robin Williams,1951 What Dreams May Come,Chris Nielsen,Robin Williams,1951 Snow Falling on Cedars,Ishmael Chambers,Ethan Hawke,1970 Ninja Assassin,Takeshi,Rick Yune,1971 Snow Falling on Cedars,Kazuo Miyamoto,Rick Yune,1971 The Green Mile,Warden Hal Moores,James Cromwell,1940 Snow Falling on Cedars,Judge Fielding,James Cromwell,1940 You've Got Mail,Patricia Eden,Parker Posey,1968 You've Got Mail,Kevin Jackson,Dave Chappelle,1973 RescueDawn,Duane,Steve Zahn,1967 You've Got Mail,George Pappas,Steve Zahn,1967 A League of Their Own,Jimmy Dugan,Tom Hanks,1956 The Polar Express,Hero Boy;Father;Conductor;Hobo;Scrooge;Santa Claus,Tom Hanks,1956 Charlie Wilson's War,Rep. Charlie Wilson,Tom Hanks,1956 Cast Away,Chuck Noland,Tom Hanks,1956 Apollo 13,Jim Lovell,Tom Hanks,1956 The Green Mile,Paul Edgecomb,Tom Hanks,1956 The Da Vinci Code,Dr. Robert Langdon,Tom Hanks,1956 Cloud Atlas,Zachry;Dr. Henry Goose;Isaac Sachs;Dermot Hoggins,Tom Hanks,1956 That Thing You Do,Mr. White,Tom Hanks,1956 Joe Versus the Volcano,Joe Banks,Tom Hanks,1956 Sleepless in Seattle,Sam Baldwin,Tom Hanks,1956 You've Got Mail,Joe Fox,Tom Hanks,1956 Sleepless in Seattle,Suzy,Rita Wilson,1956 Sleepless in Seattle,Walter,Bill Pullman,1953 Sleepless in Seattle,Greg,Victor Garber,1949 A League of Their Own,Doris Murphy,Rosie O'Donnell,1962 Sleepless in Seattle,Becky,Rosie O'Donnell,1962 The Birdcage,Albert Goldman,Nathan Lane,1956 Joe Versus the Volcano,Baw,Nathan Lane,1956 When Harry Met Sally,Harry Burns,Billy Crystal,1948 When Harry Met Sally,Marie,Carrie Fisher,1956 When Harry Met Sally,Jess,Bruno Kirby,1949 That Thing You Do,Faye Dolan,Liv Tyler,1977 The Replacements,Annabelle Farrell,Brooke Langton,1970 Unforgiven,Little Bill Daggett,Gene Hackman,1930 The Birdcage,Sen. Kevin Keeley,Gene Hackman,1930 The Replacements,Jimmy McGinty,Gene Hackman,1930 The Replacements,Clifford Franklin,Orlando Jones,1968 RescueDawn,Dieter Dengler,Christian Bale,1974 Twister,Eddie,Zach Grenier,1954 RescueDawn,Squad Leader,Zach Grenier,1954 Unforgiven,English Bob,Richard Harris,1930 Unforgiven,Bill Munny,Clint Eastwood,1930 Johnny Mnemonic,Takahashi,Takeshi Kitano,1947 Johnny Mnemonic,Jane,Dina Meyer,1968 Johnny Mnemonic,J-Bone,Ice-T,1958 Cloud Atlas,Luisa Rey;Jocasta Ayrs;Ovid;Meronym,Halle Berry,1966 Cloud Atlas,Vyvyan Ayrs;Captain Molyneux;Timothy Cavendish,Jim Broadbent,1949 The Da Vinci Code,Sir Leight Teabing,Ian McKellen,1939 The Da Vinci Code,Sophie Neveu,Audrey Tautou,1976 The Da Vinci Code,Silas,Paul Bettany,1971 V for Vendetta,Evey Hammond,Natalie Portman,1981 V for Vendetta,Eric Finch,Stephen Rea,1946 V for Vendetta,High Chancellor Adam Sutler,John Hurt,1940 Ninja Assassin,Ryan Maslow,Ben Miles,1967 Speed Racer,Cass Jones,Ben Miles,1967 V for Vendetta,Dascomb,Ben Miles,1967 Speed Racer,Speed Racer,Emile Hirsch,1985 Speed Racer,Pops,John Goodman,1960 Speed Racer,Mom,Susan Sarandon,1946 Speed Racer,Racer X,Matthew Fox,1966 Speed Racer,Trixie,Christina Ricci,1980 Ninja Assassin,Raizo,Rain,1982 Speed Racer,Taejo Togokahn,Rain,1982 Ninja Assassin,Mika Coretti,Naomie Harris,null The Green Mile,John Coffey,Michael Clarke Duncan,1957 The Green Mile,Brutus 'Brutal' Howell,David Morse,1953 Frost/Nixon,"James Reston, Jr.",Sam Rockwell,1968 The Green Mile,'Wild Bill' Wharton,Sam Rockwell,1968 Apollo 13,Ken Mattingly,Gary Sinise,1955 The Green Mile,Burt Hammersmith,Gary Sinise,1955 The Green Mile,Melinda Moores,Patricia Clarkson,1959 Frost/Nixon,Richard Nixon,Frank Langella,1938 Frost/Nixon,David Frost,Michael Sheen,1969 Bicentennial Man,Rupert Burns,Oliver Platt,1960 Frost/Nixon,Bob Zelnick,Oliver Platt,1960 One Flew Over the Cuckoo's Nest,Martini,Danny DeVito,1944 Hoffa,Robert 'Bobby' Ciaro,Danny DeVito,1944 Hoffa,Peter 'Pete' Connelly,John C. Reilly,1965 Apollo 13,Gene Kranz,Ed Harris,1950 A League of Their Own,Bob Hinson,Bill Paxton,1955 Twister,Bill Harding,Bill Paxton,1955 Apollo 13,Fred Haise,Bill Paxton,1955 Charlie Wilson's War,Gust Avrakotos,Philip Seymour Hoffman,1967 Twister,Dustin 'Dusty' Davis,Philip Seymour Hoffman,1967 Something's Gotta Give,Erica Barry,Diane Keaton,1946 Charlie Wilson's War,Joanne Herring,Julia Roberts,1967 A League of Their Own,'All the Way' Mae Mordabito,Madonna,1954 A League of Their Own,Dottie Hinson,Geena Davis,1956 A League of Their Own,Kit Keller,Lori Petty,1963

Directors

The directors.csv file contains two columns title, name, and born.

The content of the directors.csv file:

directors.csv
title,name,born Speed Racer,Andy Wachowski,1967 Cloud Atlas,Andy Wachowski,1967 The Matrix Revolutions,Andy Wachowski,1967 The Matrix Reloaded,Andy Wachowski,1967 The Matrix,Andy Wachowski,1967 Speed Racer,Lana Wachowski,1965 Cloud Atlas,Lana Wachowski,1965 The Matrix Revolutions,Lana Wachowski,1965 The Matrix Reloaded,Lana Wachowski,1965 The Matrix,Lana Wachowski,1965 The Devil's Advocate,Taylor Hackford,1944 Ninja Assassin,James Marshall,1967 V for Vendetta,James Marshall,1967 When Harry Met Sally,Rob Reiner,1947 Stand By Me,Rob Reiner,1947 A Few Good Men,Rob Reiner,1947 Top Gun,Tony Scott,1944 Jerry Maguire,Cameron Crowe,1957 As Good as It Gets,James L. Brooks,1940 RescueDawn,Werner Herzog,1942 What Dreams May Come,Vincent Ward,1956 Snow Falling on Cedars,Scott Hicks,1953 That Thing You Do,Tom Hanks,1956 Sleepless in Seattle,Nora Ephron,1941 You've Got Mail,Nora Ephron,1941 Joe Versus the Volcano,John Patrick Stanley,1950 The Replacements,Howard Deutch,1950 Charlie Wilson's War,Mike Nichols,1931 The Birdcage,Mike Nichols,1931 Unforgiven,Clint Eastwood,1930 Johnny Mnemonic,Robert Longo,1953 Cloud Atlas,Tom Tykwer,1965 Apollo 13,Ron Howard,1954 Frost/Nixon,Ron Howard,1954 The Da Vinci Code,Ron Howard,1954 The Green Mile,Frank Darabont,1959 Hoffa,Danny DeVito,1944 Twister,Jan de Bont,1943 The Polar Express,Robert Zemeckis,1951 Cast Away,Robert Zemeckis,1951 One Flew Over the Cuckoo's Nest,Milos Forman,1932 Something's Gotta Give,Nancy Meyers,1949 Bicentennial Man,Chris Columbus,1958 A League of Their Own,Penny Marshall,1943

Prerequisites

The example uses the Linux or macOS tarball installation. It assumes that your current work directory is the <neo4j-home> directory of the tarball installation, and the CSV files are placed in the default import directory.

Importing the data

Import the movies.csv file
LOAD CSV WITH HEADERS FROM 'file:///movies.csv' AS line MERGE (m:Movie {title: line.title}) ON CREATE SET m.released = toInteger(line.released), m.tagline = line.tagline
Result
Added 38 nodes, Set 114 properties, Added 38 labels
Import the actors.csv file
LOAD CSV WITH HEADERS FROM 'file:///actors.csv' AS line MATCH (m:Movie {title: line.title}) MERGE (p:Person {name: line.name}) ON CREATE SET p.born = toInteger(line.born) MERGE (p)-[:ACTED_IN {roles:split(line.roles, ';')}]->(m)
Result
Added 102 nodes, Created 172 relationships, Set 375 properties, Added 102 labels
Import the directors.csv file
LOAD CSV WITH HEADERS FROM 'file:///directors.csv' AS line MATCH (m:Movie {title: line.title}) MERGE (p:Person {name: line.name}) ON CREATE SET p.born = toInteger(line.born) MERGE (p)-[:DIRECTED]->(m)
Result
Added 23 nodes, Created 44 relationships, Set 46 properties, Added 23 labels
Create an index for nodes with the Person label
CREATE INDEX FOR (p:Person) ON (p.name)
Result
Added 1 indexes
CALL db.awaitIndexes

Index-backed property-lookup

In this example you want to write a query to find persons with the name 'Tom' that acted in a movie.

MATCH (p:Person)-[:ACTED_IN]->(m:Movie) WHERE p.name STARTS WITH 'Tom' RETURN p.name AS name, count(m) AS count
Table 1. Result
name count

"Tom Cruise"

3

"Tom Hanks"

12

"Tom Skerritt"

1

Rows:3

The query request the database to return all the actors with the first name 'Tom'. There are three of them: 'Tom Cruise', 'Tom Skerritt' and 'Tom Hanks'. With native indexes, however, you can leverage the fact that indexes store the property values. In this case, it means that the names can be looked up directly from the index. This allows Cypher to avoid the second call to the database to find the property, which can save time on very large queries.

If we profile the above query, we see that the NodeIndexSeekByRange in the Details column contains cache[p.name], which means that p.name is retrieved from the index. We can also see that the OrderedAggregation has no DB Hits, which means it does not have to access the database again.

PROFILE MATCH (p:Person)-[:ACTED_IN]->(m:Movie) WHERE p.name STARTS WITH 'Tom' RETURN p.name AS name, count(m) AS count
Query Plan
Planner COST Runtime PIPELINED Runtime version 2025.09 Batch size 128 +-----------------------+----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+ | Operator | Id | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by | Pipeline | +-----------------------+----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+ | +ProduceResults | 0 | name, count | 1 | 3 | 0 | | 0/0 | 0.060 | | | | | +----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+ | | | +OrderedAggregation | 1 | cache[p.name] AS name, count(m) AS count | 1 | 3 | 0 | 1520 | 0/0 | 3.119 | name ASC | In Pipeline 1 | | | +----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+ | +Filter | 2 | m:Movie | 1 | 16 | 32 | | | | | | | | +----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +Expand(All) | 3 | (p)-[anon_0:ACTED_IN]->(m) | 1 | 16 | 22 | | | | | | | | +----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +NodeIndexSeekByRange | 4 | RANGE INDEX p:Person(name) WHERE name STARTS WITH $autostring_0, cache[p.name] | 0 | 4 | 5 | 120 | 7/0 | 0.611 | p.name ASC | Fused in Pipeline 0 | +-----------------------+----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+ Total database accesses: 59, total allocated memory: 1600 3 rows

If we change the query, such that it can no longer use an index, we will see that there will be no cache[p.name] in the Details column, and that the EagerAggregation now has DB Hits, since it accesses the database again to retrieve the name.

PROFILE MATCH (p:Person)-[:ACTED_IN]->(m:Movie) RETURN p.name AS name, count(m) AS count
Query Plan
Planner COST Runtime PIPELINED Runtime version 2025.09 Batch size 128 +-------------------+----+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Id | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +-------------------+----+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | 0 | name, count | 13 | 102 | 0 | | 0/0 | 0.155 | In Pipeline 1 | | | +----+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +EagerAggregation | 1 | p.name AS name, count(m) AS count | 13 | 102 | 344 | 17296 | | | | | | +----+-----------------------------------+----------------+------+---------+----------------+ | | | | +Filter | 2 | p:Person | 172 | 172 | 344 | | | | | | | +----+-----------------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | 3 | (m)<-[anon_0:ACTED_IN]-(p) | 172 | 172 | 254 | | | | | | | +----+-----------------------------------+----------------+------+---------+----------------+ | | | | +NodeByLabelScan | 4 | m:Movie | 38 | 38 | 39 | 120 | 29/0 | 1.444 | Fused in Pipeline 0 | +-------------------+----+-----------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 981, total allocated memory: 17376 102 rows

For non-native indexes there will still be a second database access to retrieve those values.

Predicates that can be used to enable this optimization are:

  • Existence (e.g. WHERE n.name IS NOT NULL)

  • Equality (e.g. WHERE n.name = 'Tom Hanks')

  • Range (e.g. WHERE n.uid > 1000 AND n.uid < 2000)

  • Prefix (e.g. WHERE n.name STARTS WITH 'Tom')

  • Suffix (e.g. WHERE n.name ENDS WITH 'Hanks')

  • Substring (e.g. WHERE n.name CONTAINS 'a')

  • Several predicates of the above types combined using OR, given that all of them are on the same property (e.g. WHERE n.prop < 10 OR n.prop = 'infinity')

If there is an existence constraint on the property, no predicate is required to trigger the optimization. For example, CREATE CONSTRAINT constraint_name FOR (p:Person) REQUIRE p.name IS NOT NULL.

Aggregating functions

For all built-in aggregating functions in Cypher, the index-backed property-lookup optimization can be used even without a predicate.

Consider this query which returns the number of distinct names of people in the movies dataset:

PROFILE MATCH (p:Person) RETURN count(DISTINCT p.name) AS numberOfNames
Query Plan
Planner COST Runtime PIPELINED Runtime version 2025.09 Batch size 128 +-------------------+----+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Id | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +-------------------+----+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | 0 | numberOfNames | 1 | 1 | 0 | | 0/0 | 0.026 | In Pipeline 1 | | | +----+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +EagerAggregation | 1 | count(DISTINCT cache[p.name]) AS numberOfNames | 1 | 1 | 0 | 9888 | | | | | | +----+------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | +NodeIndexScan | 2 | RANGE INDEX p:Person(name) WHERE name IS NOT NULL, cache[p.name] | 125 | 125 | 126 | 120 | 1/0 | 1.400 | Fused in Pipeline 0 | +-------------------+----+------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 126, total allocated memory: 9952 1 row

Note that the NodeIndexScan in the Details column contains cache[p.name] and that the EagerAggregation has no DB Hits. In this case, the semantics of aggregating functions works like an implicit existence predicate because Person nodes without the property name will not affect the result of an aggregation.

Index-backed ORDER BY

Now consider the following refinement to the query:

PROFILE MATCH (p:Person)-[:ACTED_IN]->(m:Movie) WHERE p.name STARTS WITH 'Tom' RETURN p.name AS name, count(m) AS count ORDER BY name
Query Plan
Planner COST Runtime PIPELINED Runtime version 2025.09 Batch size 128 +-----------------------+----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+ | Operator | Id | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Ordered by | Pipeline | +-----------------------+----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+ | +ProduceResults | 0 | name, count | 1 | 3 | 0 | | 0/0 | 0.025 | | | | | +----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+ | | | +OrderedAggregation | 1 | cache[p.name] AS name, count(m) AS count | 1 | 3 | 0 | 1520 | 0/0 | 0.097 | name ASC | In Pipeline 1 | | | +----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+ | +Filter | 2 | m:Movie | 1 | 16 | 32 | | | | | | | | +----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +Expand(All) | 3 | (p)-[anon_0:ACTED_IN]->(m) | 1 | 16 | 22 | | | | | | | | +----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+ | | | | | +NodeIndexSeekByRange | 4 | RANGE INDEX p:Person(name) WHERE name STARTS WITH $autostring_0, cache[p.name] | 0 | 4 | 5 | 120 | 7/0 | 0.406 | p.name ASC | Fused in Pipeline 0 | +-----------------------+----+--------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+------------+---------------------+

We are asking for the results in ascending alphabetical order. The native index happens to store String properties in ascending alphabetical order, and Cypher knows this. In Neo4j 3.5 and later, the Cypher planner will recognize that the index already returns data in the correct order, and skip the Sort operation.

The Order by column describes the order of rows after each operator. We see that the Order by column contains p.name ASC from the index seek operation, meaning that the rows are ordered by p.name in ascending order.

Index-backed ORDER BY can also be used for queries that expect their results is descending order, but with slightly lower performance.

In cases where the Cypher planner is unable to remove the Sort operator, the planner can utilize knowledge of the ORDER BY clause to plan the Sort operator at a point in the plan with optimal cardinality.

min() and max()

For the min and max functions, the index-backed ORDER BY optimization can be used to avoid aggregation and instead utilize the fact that the minimum/maximum value is the first/last one in a sorted index. Consider the following query which returns the fist actor in alphabetical order:

PROFILE MATCH (p:Person)-[:ACTED_IN]->(m:Movie) RETURN min(p.name) AS name
Query Plan
Planner COST Runtime PIPELINED Runtime version 2025.09 Batch size 128 +-------------------+----+----------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | Operator | Id | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline | +-------------------+----+----------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +ProduceResults | 0 | name | 1 | 1 | 0 | | 0/0 | 0.027 | In Pipeline 1 | | | +----+----------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ | +EagerAggregation | 1 | min(p.name) AS name | 1 | 1 | 344 | 32 | | | | | | +----+----------------------------+----------------+------+---------+----------------+ | | | | +Filter | 2 | p:Person | 172 | 172 | 344 | | | | | | | +----+----------------------------+----------------+------+---------+----------------+ | | | | +Expand(All) | 3 | (m)<-[anon_0:ACTED_IN]-(p) | 172 | 172 | 254 | | | | | | | +----+----------------------------+----------------+------+---------+----------------+ | | | | +NodeByLabelScan | 4 | m:Movie | 38 | 38 | 39 | 120 | 29/0 | 0.990 | Fused in Pipeline 0 | +-------------------+----+----------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+ Total database accesses: 981, total allocated memory: 232 1 row

Aggregations are usually using the EagerAggregation operation. This would mean scanning all nodes in the index to find the name that is first in alphabetic order. Instead, the query is planned with Projection, followed by Limit, followed by Optional. This will simply pick the first value from the index.

For large datasets, this can improve performance dramatically.

Index-backed ORDER BY can also be used for corresponding queries with the max function, but with slightly lower performance.

Restrictions

The optimization can only work on native indexes. It does not work for predicates only querying for the spatial type POINT.

Predicates that can be used to enable this optimization are:

  • Existence (e.g.WHERE n.name IS NOT NULL)

  • Equality (e.g. WHERE n.name = 'Tom Hanks')

  • Range (e.g. WHERE n.uid > 1000 AND n.uid < 2000)

  • Prefix (e.g. WHERE n.name STARTS WITH 'Tom')

  • Suffix (e.g. WHERE n.name ENDS WITH 'Hanks')

  • Substring (e.g. WHERE n.name CONTAINS 'a')

Predicates that will not work:

  • Several predicates combined using OR

  • Equality or range predicates querying for points (e.g. WHERE n.place > point({ x: 1, y: 2 }))

  • Spatial distance predicates (e.g. WHERE point.distance(n.place, point({ x: 1, y: 2 })) < 2)

If there is a property existence constraint on the property, no predicate is required to trigger the optimization. For example, CREATE CONSTRAINT constraint_name FOR (p:Person) REQUIRE p.name IS NOT NULL

Predicates with parameters, such as WHERE n.prop > $param, can trigger index-backed ORDER BY. The only exception are queries with parameters of type POINT.