MySQL slow select from a view

By Posted on

Question :

I would appreciate an approach with this problem, I have a lottery system in mysql, this system receives a lot of transaction per second.. a lot! I need to check the total amount for each the number before allowing the bet, for this purpose I have a view :

CREATE ALGORITHM=MERGE DEFINER=user@% SQL SECURITY DEFINER 
VIEW view_todays_bet AS select number, sum(bet_amout) as total_bet from salestable;

With this view I have this result, for example:

Number total_bet

=====  ========

01 1500

05 2000

...

99 20

So when I have a new bet I make a select from the view and that’s the moment when the problem comes, for example if i want to know the balance for number 05, I make this select:

select total_bet from view_todays_bet where number = '05';

If I make the select directly from the main table salestable I get blocked because there are a lot of inserts incomming from other bets, but the select from the view is too slow. Please give some approach about this issue.

Thanks.

This is the real structure if the tables: Header / detail tables and a view made from a join of both.

CREATE TABLE `sales_details` (
  `codpais` varchar(2) NOT NULL DEFAULT '',
  `numero` varchar(7) NOT NULL DEFAULT '',
  `verificador` varchar(10) NOT NULL DEFAULT '',
  `codterminal` varchar(8) NOT NULL DEFAULT '',
  `item` int(4) NOT NULL DEFAULT '1',
  `codloteria` varchar(3) NOT NULL DEFAULT '',
  `secuencia` varchar(2) NOT NULL DEFAULT '' COMMENT 'Aqui van los numeros jugados',
  `codjuego` varchar(3) NOT NULL DEFAULT '',
  `numeros` varchar(30) NOT NULL DEFAULT '' COMMENT 'Aqui van los numeros jugados',
  `monto` double NOT NULL DEFAULT '0',
  `numsorteos` int(11) NOT NULL DEFAULT '1',
  `horasorteo` varchar(5) NOT NULL DEFAULT '00:00',
  `codloteria2` varchar(3) NOT NULL DEFAULT '',
  `secuencia2` varchar(2) NOT NULL DEFAULT '',
  `nombrecorto2` varchar(30) NOT NULL DEFAULT '',
  `horasorteo2` varchar(5) NOT NULL DEFAULT '',
  `numerosganadores` varchar(10) NOT NULL DEFAULT '',
  `montopremio` double NOT NULL DEFAULT '0',
  `ganaprimero` double NOT NULL DEFAULT '0',
  `ganasegundo` double NOT NULL DEFAULT '0',
  `ganatercero` double NOT NULL DEFAULT '0',
  `ganacuarto` double NOT NULL DEFAULT '0',
  `porciento` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`codpais`,`numero`,`verificador`,`codterminal`,`item`),
  KEY `codloteria` (`codloteria`),
  KEY `codjuego` (`codjuego`),
  KEY `codloteria2` (`codloteria2`)
) ENGINE=InnoDB;

CREATE TABLE `sales_header` (
  `codpais` varchar(2) NOT NULL DEFAULT '',
  `numero` varchar(7) NOT NULL DEFAULT '',
  `verificador` varchar(10) NOT NULL DEFAULT '',
  `codterminal` varchar(8) NOT NULL DEFAULT '',
  `stan` varchar(6) NOT NULL DEFAULT '',
  `tx` varchar(2) NOT NULL DEFAULT '',
  `cajero` varchar(15) NOT NULL DEFAULT '',
  `codempresa` varchar(2) NOT NULL DEFAULT '',
  `codbase` varchar(3) NOT NULL DEFAULT '',
  `codbanca` varchar(7) NOT NULL DEFAULT '',
  `codgrupo` varchar(2) NOT NULL DEFAULT '',
  `codprovincia` varchar(3) NOT NULL DEFAULT '',
  `codciudad` varchar(2) NOT NULL DEFAULT '',
  `codmunicipio` varchar(4) NOT NULL DEFAULT '',
  `codlocal` varchar(3) NOT NULL DEFAULT '',
  `total` double NOT NULL DEFAULT '0',
  `fechasorteo` date NOT NULL DEFAULT '0001-01-01',
  `fechasistema` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `fechapos` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `fechacancelacion` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `numerocancela` varchar(10) NOT NULL DEFAULT '',
  `terminalcancela` varchar(8) NOT NULL DEFAULT '',
  `turno` int(9) unsigned NOT NULL DEFAULT '0',
  `tasa` double NOT NULL DEFAULT '0',
  `esganador` varchar(1) NOT NULL DEFAULT 'N',
  `fuecobrado` varchar(1) NOT NULL DEFAULT 'N',
  `sorteocerrado` varchar(1) NOT NULL DEFAULT 'N',
  `fechacobrado` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `terminalcobrado` varchar(8) NOT NULL DEFAULT '',
  `usuariocobrado` varchar(15) NOT NULL DEFAULT '',
  `verificadorcobrado` varchar(20) NOT NULL DEFAULT '',
  `totalcobrado` double NOT NULL DEFAULT '0',
  `estatus` varchar(1) NOT NULL DEFAULT 'A' COMMENT 'A- Activo, R-Reversado, N-Anulada',
  `numrecarga` varchar(30) NOT NULL DEFAULT '',
  `autoincrecarga` varchar(4) NOT NULL DEFAULT '',
  `sincronizada` varchar(1) NOT NULL DEFAULT 'N',
  `reimpreso` varchar(1) NOT NULL DEFAULT 'N',
  `porciento` double NOT NULL DEFAULT '0',
  `bk` varchar(1) NOT NULL DEFAULT 'N',
  `codcliente` varchar(5) NOT NULL DEFAULT '',
  `calcporciento` varchar(1) NOT NULL DEFAULT 'T',
  PRIMARY KEY (`codpais`,`numero`,`verificador`,`codterminal`),
  KEY `stan` (`stan`),
  KEY `tx` (`tx`),
  KEY `fechasorteo` (`fechasorteo`),
  KEY `fechasistema` (`fechasistema`),
  KEY `estatus` (`estatus`),
  KEY `codconsorcio` (`codbase`),
  KEY `codempresa` (`codempresa`),
  KEY `codbanca` (`codbanca`),
  KEY `codgrupo` (`codgrupo`),
  KEY `bk` (`bk`),
  KEY `codcliente` (`codcliente`),
  KEY `esganador` (`esganador`),
  KEY `numrecarga` (`numrecarga`)
) ENGINE=InnoDB;

CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`%` SQL SECURITY DEFINER VIEW `view_todays_bet` AS select `vd`.`codpais` AS `codpais`,`vd`.`codloteria` AS `codloteria`,`vd`.`codjuego` AS `codjuego`,sum(`vd`.`monto`) AS `monto`,`fnNumeroCRC`(`vd`.`numeros`) AS `crc` from (`sales_details` `vd` join `sales_header` `vh`) where ((`vd`.`codpais` = `vh`.`codpais`) and (`vd`.`numero` = `vh`.`numero`) and (`vd`.`verificador` = `vh`.`verificador`) and (`vd`.`codterminal` = `vh`.`codterminal`) and (`vh`.`fechasorteo` = date_format(sysdate(),'%Y-%m-%d')) and (`vh`.`estatus` = 'A')) 
group by 1,2,3,5 
order by 1,2,3,5

Answer :

There are a few things going on here:

  • your CREATE VIEW statement contains a SUM() function. Even though your ALGORITHM is specified as MERGE, it is really going to be TEMPTABLE as discussed in the documentation here:

    If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs:
    Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

  • So we’re using temporary tables. I suspect you’re not seeing ‘blocking’ due to this benefit of TEMPTABLE algorithm:

    A reason to choose TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long.

  • However, the problem with the TEMPTABLE algorithm is that they do not have/use indexes [src]:

    View processing is not optimized:

    It is not possible to create an index on a view.

    Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

So now we need to know the underlying structure of your table as to why the direct query blocks:

  • If it’s not InnoDB, consider switching to use row-level locks instead of table-locks of MyISAM
  • If there’s no index on (number, bet_amount) consider creating one.

If the above points have already been done, and you still for some reason have blocking on a query directly against the salestable table, then you can institute a poor-man’s materialized view by creating triggers that modify a temporary table with each number and a running total.

Slow views

RESOLVED WITH ALTERNATIVE SOLUTION

A functional alternative would be to encapsulate the view query in a procedure, with parameter passing. This is a simple solution to improve the problem of slow VIEWS with multiple joins queries between multiple tables.

DELIMITER ;;
CREATE PROCEDURE `SP_QUERY_VIEW_WITH_PARAMETERS`(IN p_having VARCHAR(300))
    COMMENT 'Executes the statement'
BEGIN
  SET @v_having = p_having;
  SET @v_sql=CONCAT('SELECT 
                    id    AS id_emp  ,
                    user  AS emp_name,
                    .
                    .
                    .
                    FROM table1
                    UNION ALL
                    SELECT 
                    idtifier_us    AS id_emp  ,
                    description    AS emp_name,
                    .
                    .
                    .
                    FROM table2');
SET @v_sql2 = CONCAT(@v_sql,@v_having);
  PREPARE stmt FROM @v_sql2;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;

CALL `SP_QUERY_VIEW_WITH_PARAMETERS`('having id_emp=63 and emp_name like ''VANDERLEI%'' and created_at between ''2019-05-01'' and ''2019-05-17''  ');

this alternative preserves the optimization by index in the execution of the query

NOTA: “where” it can also be used, but with simpler queries

Leave a Reply

Your email address will not be published. Required fields are marked *