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-leave@lists.mariadb.org
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org