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
Best Answer
There are a few things going on here:
your
CREATE VIEW
statement contains aSUM()
function. Even though yourALGORITHM
is specified asMERGE
, it is really going to beTEMPTABLE
as discussed in the documentation here:So we're using temporary tables. I suspect you're not seeing 'blocking' due to this benefit of TEMPTABLE algorithm:
However, the problem with the
TEMPTABLE
algorithm is that they do not have/use indexes [src]:So now we need to know the underlying structure of your table as to why the direct query blocks:
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.