We need to take a step back and think about what Backlight really is. As per our product page, “Backlight is a unique website creation and photo publishing platform, allowing photographers to create, manage and personalize their online photo galleries.” This isn’t a tool for hosting large volumes of data. It’s not a tool for fine-grained and performant searching of large volumes of data.
I can see where you’re coming from. I too have a background in large and complex data sets*. My day job is for a tool that selects and transforms data from hundreds of tables with millions of rows and returns results to hundreds of concurrent users in a matter of seconds. But that system is entirely different in its purpose and nature to Backlight.
(* of course, what one person considers large and complex may be considered as trivial to another. The sky is the limit with data.)
The data structures in Backlight are kept fairly simple. The design has been carried through in various forms from entirely different back-ends, from CE2 to CE4, and Backlight 1 through 4. We’ve provided upgrade paths across those different versions. So for legacy reasons, it’s kept simple and compatible. The performance issues you’re encountering are likely scalability issues from our use of SQLite. We love SQLite, as it provides a robust and efficient way of searching through photos for the use case that Backlight is designed for. Most importantly, it provides a zero-config way of providing a backend database. The common Linux hosting database of MySQL is not something we want our customer base to deal with and not something we have the resources or inclination to support. But, as you’ve found, SQLite does not scale well to large amounts of data.
Similarly, search is kept simple. It does what it needs to do for a photography website. This is reflected in the provided functionality and accompanying documentation.
I’m not suggesting that it can’t be improved, but that your use case is pushing beyond what Backlight is intended for. It’s closer to something that would be better handled by a bespoke system tailored to your specific needs.