Skip to main content

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

17
  • Thank you Rick. I'll try what you have suggested and report! Commented Dec 4, 2020 at 21:23
  • Ok, here are my results: by leaving those table as MyISAM, I get an astonishing improvement by just using your suggested query "select a.* from HLAsset a where a.excluded=0 AND a.date_deleted='' AND a.filetype='SCORCH' AND EXISTS (SELECT 1 FROM HLAssetCategory WHERE asset_id = a.asset_id)" which takes less than 4 seconds to complete. Strangely enough, once I have switched both tables to InnoDB the performance dropped to less than 6 seconds. I tried to add compound indexes on the compared columns but made no difference. Your thoughts on that? Commented Dec 4, 2020 at 23:15
  • @FabrizioFerrari - If you remove the AND EXISTS(...) do you get the 'correct' results? Or do you get too many rows? (And what is the speed like?) Commented Dec 5, 2020 at 1:08
  • Well, that's even weirdier... it is taking longer without the AND EXISTS(...)!! Well over 11 seconds! Why's that? Doesn't make any sense to me... that with InnoDB tables. on MyISAM I get less than 3 seconds without the AND EXISTS(...)... awaiting for your thoughts! Commented Dec 5, 2020 at 19:44
  • @FabrizioFerrari - But does it deliver too many rows? Perhaps the sheer quantity of rolls is causing the 11 seconds? Commented Dec 5, 2020 at 21:35