Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Can you add table definition for 'salestable': SHOW CREATE TABLE salestable\G –  Derek Downey Oct 26 '13 at 2:52
1  
I suppose the view has a GROUP BY number that was mistakenly not included in the code. –  ypercube Oct 26 '13 at 8:22
    
Check your view name (view_todays_bet) it looks you only should have data there off today ? why don't you have filter this in your view?? select number, sum(bet_amout) as total_bet from salestable; no filter where.. and where is the GROUP BY like ypercude already mentioned? –  Raymond Nijland Oct 26 '13 at 20:26
    
@user2286970 can you give us more information about your table... i've made an assumption here sqlfiddle.com/#!2/1c430/7 based on the profile timing i would say that opening tables is most expensive in your query and DERIVED tables is indeed creating an temporary table to hold results also there is an profile item with "removing tmp table" and the EXPLAIN line with id 2 and DERIVED with type: index rows 4 Extra: using index is also bad this tells me MySQL needs to do an FULL INDEX SCAN –  Raymond Nijland Oct 26 '13 at 20:29

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.