Re: index not being pick up

[Øystein Grøvlen]

Raf Mahn Wrote:
-------------------------------------------------------
> If I change the index to be (reference, scope_id),
> it will pick up index. Though, since scope_id is
> our column to make the table multi-tenancy, we
> wanted to keep it on the left as I'm hoping to be
> able to use the scope_id for other queries instead
> of needing to create more indexes in the future.

Note that sticking with the original index, also means that no index could be used when @scope_override_enabled is set.

> Seems like creating multiple views is way to go.

An alternative to using a view, could be to use a stored procedure and build the query string dynamically:

delimiter //
CREATE PROCEDURE vw (IN pred VARCHAR(255))
BEGIN
DECLARE str VARCHAR(255) DEFAULT 'SELECT * FROM property WHERE (';
IF NOT scopeOverrideEnabled() THEN
SET str = CONCAT(str, 'scope_id = getScopeId()) AND (');
END IF;
SET @query = CONCAT(str, pred, ')');
PREPARE stmt FROM @query;
EXECUTE stmt;
DROP PREPARE stmt;
END //
delimiter ;

You can then pass in the specific predicate like this:

call vw("reference = '200ADELAIDE'");

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway