Gossamer Forum
Quote Reply
SQL Index problems
I am trying to duplicate 3 fields that currently exist in their own table, and add them to lsql_Links Table.
I have added the columns, but I am having difficulty with the indexes.

The table I am trying to dup has this for indexes...


When I run this SQL code using a separate table named 'lsql_ZipCodes', it works fine(via phpMyAdmin)
Code:
SELECT o.ZipCode, o.State,

(3956 * (2 * ASIN(SQRT(

POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) +

COS(z.Latitude*0.017453293) *

COS(o.Latitude*0.017453293) *

POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2)

)))) AS Distance



FROM lsql_ZipCodes z,

lsql_ZipCodes o,

lsql_ZipCodes a



WHERE z.ZipCode = 54720 AND

z.ZipCode = a.ZipCode AND

(3956 * (2 * ASIN(SQRT(

POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) +

COS(z.Latitude*0.017453293) *

COS(o.Latitude*0.017453293) *

POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2)

)))) <= 50



ORDER BY Distance

The output gives me...


PERFECT... So, now I have added Zip, Longitude and Latitude to lsql_Links table.
I have modified the SQL code to this (diffs in red). I essentially just changed lsql_ZipCodes to lsql_Links (Also, my column is called Zip, not ZipCode)
Code:
SELECT o.Zip, o.State,

(3956 * (2 * ASIN(SQRT(

POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) +

COS(z.Latitude*0.017453293) *

COS(o.Latitude*0.017453293) *

POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2)

)))) AS Distance



FROM lsql_Links z,

lsql_Links o,

lsql_Links a



WHERE z.Zip = 54720 AND

z.Zip = a.Zip AND

(3956 * (2 * ASIN(SQRT(

POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) +

COS(z.Latitude*0.017453293) *

COS(o.Latitude*0.017453293) *

POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2)

)))) <= 50



ORDER BY Distance


The output gives me...


Note that I do not have "Distance", and the results are just wrong.

The only thing I can find that is different between the separate table 'lsql_ZipCodes' and 'lsql_links' is the indexes.
lsql_ZipCodes indexes are shown above (1st pic).

For lsql_Links indexes I currently have this ( I do not want to loose PRIMARY key of ID) and screw up the Links table



So... if I click on "Explain SQL" after running the 1st query above (the working one with the separate table lsql_ZipCodes), it shows me the following


When I click on "Explain SQL" with the second query (the messed up one using lsql_Links), it shows me this


So... :-)
This seems to be an index issue and I am wondering if someone can steer me in the right direction

Thanks
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] SQL Index problems In reply to
Never mind :-)

FYI, part of the problem with the output from the lsql_Links table version, had to do with missing (empty) zip codes.

I have decided to *NOT* copy longitude and latitude into the lsql_Links table at all. Find my other posts on this.

What will happen when I delete images in prev post from my server.

Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com