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. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
