search

openpodcast / api

OpenPodcast API server
https://openpodcast.dev
8 stars 2 forks source link

Update SQL variable handling #165

Closed woolfg closed 6 months ago

woolfg commented 6 months ago

At the beginning, we considered using MySQL's built-in variable system to replace podcast_id, and the start/end dates. The idea was to leverage the internal optimizer, which might have been capable of more efficient processing by recognizing these as variables. However, the outcome was the opposite. The optimizer became confused, leading to incorrect decisions that resulted in significantly longer query execution times—sometimes even resulting in timeouts of multiple minutes. This PR introduces variable replacement directly during SQL query generation on the client side, which should generally lead to faster execution times.

woolfg commented 6 months ago

deployed. report generation went down from 21sec for podcast 16 to 10sec.