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