Thanks Gordan,
I'll look into ProxySQL first, thank you.
It's not "no scope", just insanely hard scope due to the entity manager
that does the SQL and the code where it is used is separated by several
layers.
I think you confirmed my option below exceptionally well, thank you.
And now I know about the materialized view terminology.
Kind regards,
Jaco
On 2025/05/06 16:24, Gordan Bobic wrote:
If you have no scope to change the query in the application your
options are limited to:
1) Use a materialized view rather than a regular view (static table
that contains the output of a view, maintained by triggers)
2) Query rewrite in a proxy you put in front of MariaDB. MySQL has a
query-rewrite plugin, but MariaDB does not hence why you would have to
put a proxy in front of it. Both ProxySQL and MaxScale have a feature
for query-rewriting.
On Tue, 6 May 2025 at 17:19, Pantelis Theodosiou <[email protected]> wrote:
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
<[email protected]> 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
<[email protected]> 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 -- [email protected]
To unsubscribe send an email to [email protected]
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
