Hi Arnaud, > what is the field type?
The field type is a real number. There are so many similar values in terms of the integer part but many many variations of the decimal part. Currently I am using a B-Tree index. I’ve thought about breaking it the two parts but that just adds an extra field and storage and if I did that then maybe a Cluster index would be obvious. I'll play with the Query plan and see if it’s helpful. Having almost 5 million cities is a good testing ground and a good reference database when I am matching smaller sets of GEO data (currently imported and massaged from another database). In this case I am searching by country (or country code) and longitude to find (if found) an Olson TimeZone. The fastest search I've found so far (B-Tree index): // [PE_Geo_Location]lon_dec = 28.6 // [PE_Geo_Location]Country_Code = RU GEO_GetTimeZone([PE_Geo_Location]Country_Code;[PE_Geo_Location]lon_dec) $cntryCode:=$1 $lon_r:=Int($2) // 28 QUERY([City];[City]country code=$cntryCode) QUERY SELECTION([City];[City]longitude>=$lon_r;*) QUERY SELECTION([City]; & ;[City]longitude<=$lon_r) …. $0:=[City]timezone It’s not a “live” database so I don't want to spend too much time with it. But when I need to match data then, of course, I want it to search as fast as is possible. Appreciate, John… >> Le 10 avr. 2020 à 02:03, John J Foster via 4D_Tech <[email protected] >> <mailto:[email protected]>> a écrit : >> >> Hi All, >> >> I have a GEO database of almost 5 million cities. They include various data >> and latitude and longitude. >> >> I need to use the Longitude file in searches. To speed it up I need to >> index. Many of the values are similar like 141.235 or 141 3.23 and so on. So >> for those who might know would a Cluster index be better suited or a B-Tree? > > I'd try distinct values first, a cluster is supposed to be better when > there's enough repeated values. And most probably I'd try… I didn't > understand from "141.235 or 141 3.23": what is the field type? > You may also consider using another field with "simplified value" from > longitude, depending on what's expected from that search. Or, if the search > is always combined with other fields than longitude, make some tests and > analyse query plan/query path to see if that index is required. > > -- > Arnaud de Montard ********************************************************************** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
