Why not two separate queries then, and modify the application code to combine the results?
SELECT * FROM table WHERE p1 = 'p1' ; SELECT * FROM table WHERE p2 = 'p2' ; On Tue, May 6, 2025 at 3:14 PM Jaco Kroon via discuss < discuss@lists.mariadb.org> wrote: > Hi, > > On 2025/05/06 16:07, Gordan Bobic wrote: > > Is there an overwhelming reason why can you not instead search for: > > > > SELECT * FROM view_name WHERE p1 = 'p1' and p2 = 'p2'? > > The application in use configures a table name only, and assumes a > single id column with a string value against which to match. > > Fixing that is going to be extremely complex (read: I don't see a way of > achieving that, even with access to the code, was my first thought). > > Kind regards, > Jaco > > > > > On Tue, 6 May 2025 at 16:58, Jaco Kroon via discuss > > <discuss@lists.mariadb.org> wrote: > >> Hi All, > >> > >> So I'm stuck with an application that does something like (can't > >> trivially modify the code): > >> > >> Prepare SELECT * FROM view_name WHERE id = ? > >> Execute SELECT * FROM view_name WHERE id = 'p1-p2' > >> > >> The problem is that id is a compound between two individual indexes from > >> the underlying tables, eg: > >> > >> CREATE VIEW AS SELECT CONCAT(k1, '-', k2) id, other, fields FROM table1, > >> table2; > >> > >> Yes, the full join is on purpose, table2 will only ever have a handful > >> (three currently) rows in it. > >> > >> table1 however can get "big" (only a few hundred rows, and will likely > >> mostly be in the buffer pool on all hosts, so most likely not the worst > >> possible situation). > >> > >> Is there perhaps a mechanism to get mariadb to "break down" the searched > >> for id value to enable it hitting the underlying indexes more properly? > >> > >> If not this is going to be a "let's see how far it stretches" kind of > >> scenario for the time being, or possibly see if the mariadb query cache > >> can help. In which case, is it possible to selectively enable the query > >> cache on a per query basis, because as a rule we found that the query > >> cache actually degraded performance (I believe due to lock contention). > >> > >> The only other option I can imagine is to create a separate table that > >> regenerates whenever data in the source tables changes (by way of > triggers). > >> > >> Kind regards, > >> Jaco > >> > >> _______________________________________________ > >> discuss mailing list -- discuss@lists.mariadb.org > >> To unsubscribe send an email to discuss-le...@lists.mariadb.org > > > > > _______________________________________________ > discuss mailing list -- discuss@lists.mariadb.org > To unsubscribe send an email to discuss-le...@lists.mariadb.org >
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-le...@lists.mariadb.org
