Re: [sqlite] Help speeding up very slow query

[Angelo Brigante, Jr.]

D. Richard Hipp wrote:
What's the schema?  Do you have an index? 

CREATE TABLE Master ( playerID text, managerID text, hofID text,
birthYear integer, birthMonth integer, birthDay integer,
birthCountry text, birthState text, birthCity text,
deathYear integer, deathMonth integer, deathDay integer,
deathCountry text, deathState text, deathCity text, nameFirst text,
nameLast text, nameNote text, nameGiven text, nameNick text,
weight integer, height real, bats text, throws text, debut text,
college text, lahman40ID text, lahman45ID text, retroID text,
holtzID text, bbrefID text);
CREATE TABLE Batting ( playerID text, yearID integer, stintID integer,
teamID text, lgID text, G integer, AB integer, R integer, H integer,
DBL integer, TPL integer, HR integer, RBI integer, SB integer,
CS integer, BB integer, SO integer, IBB integer, HBP integer,
SH integer, SF integer, GIDP integer);
I did not try to use indexes at all (yet).
Here's another approach to the query:
   SELECT nameLast, hrtotal
   FROM (SELECT playerid, sum(hr) AS hrtotal
         FROM batting
         GROUP BY playerid) AS a, master
   WHERE hrtotal>=500 AND master.playerid=a.playerid;
This query worked great. Thanks. Guess I have a lot to learn still.
I appreciate the quick response. Thank you.
Angelo