Is it possible to block a specific query, or return a static result?

Before you yell at me for doing it wrong, I know I'm doing it wrong. I'm using WHMCS (source code is not available), and we have a quarter of a million invoices. One particular query, which runs on every page, is absolutely murdering the database.

SELECT 
    SUM(amountin-amountout) 
FROM tblaccounts 
WHERE invoiceid IN (
                    SELECT 
                      id 
                    FROM tblinvoices 
                    WHERE 
                      userid=*userid* AND status='Unpaid'
                   )

This generates the logged-in user's total unpaid invoice sum. Problem is, due to the nested queries, each pageload is running over 450k queries (the amount of rows in tblaccounts), each of which is filtering over 750k rows (the amount of rows in tblinvoices).

Can this be blocked at the database server level? Or ideally, set to return a specific value?

Server version: 5.5.41-0+wheezy1-log - (Debian)

Protocol version: 10

The tables are using the MyISAM engine. Here are the CREATE TABLE statements:

tblaccounts

CREATE TABLE `tblaccounts` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `currency` int(10) NOT NULL,
  `gateway` text NOT NULL,
  `date` datetime DEFAULT NULL,
  `description` text NOT NULL,
  `amountin` decimal(10,2) NOT NULL DEFAULT '0.00',
  `fees` decimal(10,2) NOT NULL DEFAULT '0.00',
  `amountout` decimal(10,2) NOT NULL DEFAULT '0.00',
  `rate` decimal(10,5) NOT NULL DEFAULT '1.00000',
  `transid` text NOT NULL,
  `invoiceid` int(10) NOT NULL DEFAULT '0',
  `refundid` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `invoiceid` (`invoiceid`),
  KEY `userid` (`userid`),
  KEY `date` (`date`),
  KEY `transid` (`transid`(32))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

tblinvoices

``
CREATE TABLE tblinvoices (
id int(10) NOT NULL AUTO_INCREMENT,
userid int(10) NOT NULL,
invoicenum text NOT NULL,
date date DEFAULT NULL,
duedate date DEFAULT NULL,
datepaid` dateti

As far as I can tell the answer to this question is no. You can't block this SELECT at the database level, or rewrite it in versions before 5.7.6 (dev milestone 16 for the 5.7 release).

If the offending statement were an UPDATE, INSERT, or DELETE then you could use the tactics described in this StackOverflow answer to nullify the statement via a trigger.

As established in this answer (also on StackOverflow), you can't put a trigger on a SELECT statement.

Given the above is true, you will have to do one of three things:

• Put a proxy like MySQL Proxy (that proxy was discovered by Dr. McKay) between the database and the application that will replace the query and return a cached/static result as suggested by jkavalik initially.

• Wait for your support ticket to work it's way through WHMCS's bug system.

• Figure out how to optimize this query until it isn't a problem anymore or option 1/2 are in place.

We can't help with option 1 or 2, but we can help you optimize your query. Optimizing the query may bring the execution time down from tenfold, which will lessen your organization's pain, and make you look like a hero. I vote you take this route while you are waiting for the bug ticket if you can't install the proxy or get it working.

StackExchange Database Administrators Q#114616, answer score: 4