On 2017-11-03 20:21, Frank Cazabon wrote:

Individual indexes on each field should work, not an index on the compound expression.

Yep. That's what I found. Here's a subset of my indexes:

index on city tag city index on state tag state index on deleted() tag mydel BINARY

And then here's my query results from SYS(3054,12,"memvar"):

SELECT o1.invoice, o1.vendor, o1.vendor_id, o1.del_date, o1.equipment, o1.gross, o1.cost, o1.pu_city, o1.pu_st, o1.dest_city, o1.dest_st, o1.qty, o1.product, o1.ven_miles, o1.weight, o1.entered, o1.ven_fha, o1.dot_no, o1.ven_city, o1.ven_state, o1.ven_zip, o1.ven_poc, o1.ven_phone, o1.ven_fax, o1.ven_email, 0 as stop_flag, m.tlAlternate as Alt_flag FROM tmpDetail o1 WHERE pu_city = m.tcPu_City AND pu_st = m.tcPu_St INTO CURSOR Query1 Using index tag Pu_city to rushmore optimize intermediate result Using index tag Pu_st to rushmore optimize intermediate result Using index tag Mydel to rushmore optimize intermediate result Rushmore optimization level for intermediate result: full

Note that without the DELETED() index tag, it would have said Rushmore was PARTIAL.

On 2017-11-03 21:21, Frank Cazabon wrote:

Yes, the deleted () index makes it full but may actually slow the query down in real life so you need to test it on realistic data to see if you should keep it or not.

Right. In my case, the percentage of deleted records is near none, so even though it's a BINARY index (smaller, more efficient), it may be a slow down since really no records deleted.

Hi all,

As I recall, as long as the value to the left of the "=" matches a key, then Rushmore will use it.

So if you use a compound key (index on city + state + padl(allt(str(radius)),6,'0') tag MyTag) , it will only be fast if you use something like:

Select * from MyTable where city + state + padl(allt(str(radius)),6,'0') = "some value with padded spaces, etc."

In most cases, this would be silly. But there are exceptions - yours is not apparently one of them as indexes on the individual fields will always be faster.

Keep in mind that if you have an index on radius or even str(radius), the following still will not be optimized: Select * from MyTable where padl(allt(str(radius)),6,'0') = " 342342"

You would have to actually have an index build on padl(allt(str(radius)),6,'0') - not very useful.

The key question is, once you have filtered by city and state, how many records will you then have? If many (>10,000 or so), then an additional filter option might help. But VFP can filter 10,000 (and usually much more) records very fast, even without indexes - so only testing would help.

Anyway, hope things worked out for you,

Fletcher

Fletcher Johnson FletcherSJohnson@Yahoo.com LinkedIn.com/in/FletcherJohnson beknown.com/FletcherJohnson twitter.com/fletcherJ strava.com/athletes/fletcherjohnson 408-946-0960 - work 408-781-2345 - cell

-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Friday, November 3, 2017 5:22 PM To: profox@leafe.com Subject: Re: Reminder about VFP compound indexes and best strategy

Individual indexes on each field should work, not an index on the compound expression.

On 3 November 2017 17:09:39 GMT-04:00, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:

VFP9SP2 free table

I've got a table with this structure:

city c(30) state c(2) radius i distance i

My query is looking for city, state, and radius, to get back those whose distance is <= InputParameter.

select * from MyTable where city = m.tcCity and state = m.tcState and radius <= m.tiRadius

Not sure if I should index on city tag city index on state tag state index on radius tag radius

or

index on city + state + padl(allt(str(radius)),6,'0') tag MyTag

???

I know I can use SYS(3054) to get my answer but I'm getting ready to be

away from the office for a bit and was hoping someone knew the obvious answer. Otherwise I'll run my testing later tonight.

(At the very least, hopefully somebody learned about SYS(3054,x,y) today.)

[excessive quoting removed by server]