Thank you all for your responses, Igor - using a UNION made a huge difference, thank you.
I have a quick query on from the suggestion of adding indices for anything that appears either side of an equals sign. I have many user-selectable terms that can be added to the query. Is it worth adding indices for all of these? I have heard having too many indices can slow searches down, so I want to know where to stop, or is there no hard-and-fast rule, just test and see? Regards, Ed On 06/08/2010 13:22, Igor Tandetnik wrote: > Edward Hawke<edhawk...@googlemail.com> wrote: >> I have a query that is working at an acceptable speed, and I need to add >> something else to it. As soon as I add the extra terms it grinds to a halt >> (taking well over 5 minutes to perform the query on a relatively small >> dataset c.100,000 records). > But because you repeat the same table three-four times in the FROM clause, > thus building a cross-product, you are effectively working with a dataset of > some 10^15 records. Anything that suppress the use of indexes by SQLite will > cause performance to tank. > >> My acceptably fast query is fairly complicated as it pulls information from >> a number of different tables, linking them all using WHERE clauses (I've >> read somewhere this is the fastest way to do this in SQLite). It is shown >> below (apologies for it's length) though I have cut out the information >> about which columns it is selecting as it makes it too long to comfortably >> read. >> >> *SELECT*<Multiple columns from each table> *FROM* Delivery, Match, Tour, >> Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam >> PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam, >> Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID = >> Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = >> BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = >> BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND >> BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND >> Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = >> Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = >> AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID >> AND Batsman.ID = 1234567890); >> >> There are often multiple IDs specified at the end of the WHERE clause, as >> these are added depending upon selections a user has made from a GUI. >> >> In a very specific case I need to select the above plus one unrelated row. >> Therefore my query changes to (for example): >> >> *SELECT*<Multiple columns from each table> *FROM* Delivery, Match, Tour, >> Ground, Country, PlayerTeam BowlerPT, PlayerTeam BatsmanPT, PlayerTeam >> PartnerPT, Player Bowler, Player Batsman, Player Partner, Team BowlingTeam, >> Team BattingTeam, Team HomeTeam, Team AwayTeam *WHERE* (Delivery.MatchID = >> Match.ID AND Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = >> BowlerPT.ID AND BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = >> BowlingTeam.ID AND Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND >> BatsmanPT.PlayerID = Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND >> Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = >> Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = >> AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID >> AND Batsman.ID = 1234567890) *OR* (Delivery.MatchID = Match.ID AND >> Match.TourID = Tour.ID AND Delivery.BowlerPlayerTeamID = BowlerPT.ID AND >> BowlerPT.PlayerID = Bowler.ID AND BowlerPT.TeamID = BowlingTeam.ID AND >> Delivery.BatsmanPlayerTeamID = BatsmanPT.ID AND BatsmanPT.PlayerID = >> Batsman.ID AND BatsmanPT.TeamID = BattingTeam.ID AND >> Delivery.PartnerPlayerTeamID = PartnerPT.ID AND PartnerPT.PlayerID = >> Partner.ID AND Match.HomeTeamID = HomeTeam.ID AND Match.AwayTeamID = >> AwayTeam.ID AND Match.GroundID = Ground.ID AND Tour.CountryID = Country.ID >> AND Delivery.ID = 1987654321); > Using OR pretty much kills optimization in SQLite. Run this as two separate > queries, or as a UNION query with two subqueries, each of which only uses AND. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
