17K records in 1m45s

[wkpride]

Update and question(s).

I fiddled with INNER JOIN yesterday and was able to get everything working. 30K records in 5min and 79K took just over 30min. I changed the code as follows: I added a few extra's to help me test results.

SELECT a.sysid, b.sysid, a.polenum, b.polenum, a,scname, b.scname
FROM kenpoles AS a
INNER JOIN kenpoles AS b
ON((a.polenum=b.polenum)AND(a.scname=b.scname)AND(a.sysid<>b.sysid))
WHERE a.scname = 'Morgantown'
GROUP BY b.sysid;

My first question is (because I haven't used SQL much): Could i qualify the entire statement by starting with WHERE?

As in:

WHERE (a.scname='Morgantown') AND (b.scname='Morgantown')
SELECT a.syid, b.sysid........ so on

I'm pretty sure it has to go through the entire db (1.6m) testing for a.scname='Morgantown' - Which is the slowdown. If I had db's with 50k records each, I bet the result would be less than 1 minute...

Last item. How can I dump the results into another table? I tried adding an INSERT INTO table VALUES b.polenum...

That's about it. Thanks for everything! kp

[Vali]

SELECT a.sysid, b.sysid, a.polenum, b.polenum, a,scname, b.scname
FROM kenpoles AS a, kenpoles AS b
WHERE a.scname='Morgantown'
AND a.polenum=b.polenum
AND a.scname=b.scname
AND a.sysid!=b.sysid
GROUP BY b.sysid;

Also, try adding some indexes to your DB, since this query should take less than a second.

Ps: your WHERE conditions don't make much sense.