How a graph DB index the data to support this query?

Usually there are indices on the properties of specific labels (e.g. for the .population of :Country), in graph databases with labeled nodes (most of them), so in general pretty much the same as in a relational database.

Is that the shortest way to write it in Cypher?

SQL needs 48 chars:

    SELECT name FROM cities WHERE population>1000000

Cypher needs 57 chars:

    MATCH (n:cities) WHERE n.population>1000000 RETURN n.name

I would be very hesitant to use a language that needs 19% more code to express the same thing.

This example doesn't demonstrate any of the power of Cypher, the whole point is matching a pattern than corresponds to a traversal, which as it happens is much much more elegant than recursive SQL

Would be nice though to have an example that does demonstrate the power of Cypher.

SELECT p.ProductName FROM Product AS p JOIN ProductCategory pc ON (p.CategoryID = pc.CategoryID AND pc.CategoryName = "Dairy Products")

JOIN ProductCategory pc1 ON (p.CategoryID = pc1.CategoryID) JOIN ProductCategory pc2 ON (pc1.ParentID = pc2.CategoryID AND pc2.CategoryName = "Dairy Products")

JOIN ProductCategory pc3 ON (p.CategoryID = pc3.CategoryID) JOIN ProductCategory pc4 ON (pc3.ParentID = pc4.CategoryID) JOIN ProductCategory pc5 ON (pc4.ParentID = pc5.CategoryID AND pc5.CategoryName = "Dairy Products");

-----------------------------------------------

MATCH (p:Product)-[:CATEGORY]->(l:ProductCategory)-[:PARENT*0..]->(:ProductCategory {name:"Dairy Products"}) RETURN p.name

So basically allow 'named joins' in SQL and we are done.