Hi all, 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).
I've looked into the speed optimisations suggested on the site and tried a few but can't seem to speed it up. I think setting better indices will help me, but I'm fairly green when it comes to choosing which will be best for a query. 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); This is the query that takes an unbelievable amount of time to process compared to the approximately 10 seconds the first query does. Does anyone have any suggestions for what indices to use or how to change this query to speed it up? I have done an EXPLAIN QUERY PLAN on the query, which gave me the following: "0","2","TABLE Tour" "1","4","TABLE Country" "2","1","TABLE Match" "3","11","TABLE Team AS BowlingTeam" "4","12","TABLE Team AS BattingTeam" "5","13","TABLE Team AS HomeTeam" "6","14","TABLE Team AS AwayTeam" "7","3","TABLE Ground" "8","8","TABLE Player AS Bowler" "9","9","TABLE Player AS Batsman" "10","10","TABLE Player AS Partner" "11","5","TABLE PlayerTeam AS BowlerPT" "12","6","TABLE PlayerTeam AS BatsmanPT" "13","0","TABLE Delivery VIA MULTI-INDEX UNION" "14","7","TABLE PlayerTeam AS PartnerPT" "0","0","TABLE Delivery WITH INDEX Delivery_BatsmanPTIdx" "0","0","TABLE Delivery WITH INDEX sqlite_autoindex_Delivery_1" But I am unsure on how to read this, and what information it is giving me about how to better select my indices. Thanks in advance for all your help, Ed _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
