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 <yperc...@gmail.com> 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 > <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 -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-le...@lists.mariadb.org
