mysql - how to do a join query for huge tables? -


Hope someone can help me with this question. I need a list of all 200 records from ngr_titles below, join with your most recent related records from ngr_monitordata . The foreign key in Ngr_monitordata is called titid and most recently for each title should be when posted is the problem. The problem is that ngr_monitordata and every other is growing.

This is the query I used, but it takes 14 seconds to execute: 

  SELECT * ngr_titles to LEFT JOIN (SELECT titleid, MAX (when_ Posted ) Title by ngr_monitordata group) tmp1 ON ngr_titles.id = tmp1.titleid;   
 What will be the best way?  
 Tables are:  
  create tab 'ngr_titles` (`id` int (11) null auto_ignment,` title' varchar (255) default faucet, `drop_mina 'et (11) No tap default '50', `warning_pcnt float (5,1) No tap default '15 .0 ',` crit_pcnt` float (5,1) tap default default '25 .0', `deviation` float 5) Faucet default '2.0', 'adlobie' int (2) tap default '0', 'url' varchar (255) default faucet, 'state' enum ('ok', 'warning', 'important' 'Graph_arnal' varchar (255) default zero, `last review '11', not the default '0', 'short_name' varchar (45), 'historic' varchar (45) ) No tap, `cacti_url varchar (255) default faucet, primary key (` id`), unique key` title` (`title`)) engine = InnoDB default charge = UTF8;` `NGRMTART (` ID` int ( 11) Null auto_interpretation, `titled 'at (11) default noise,` When transmitted' INT (11) default noise, `games_p Default_all, `games_in_progress_gps` int (11) default null,` users_in_games` int (11) default tap, `` (11) default name, `games_created` Users_in_games_gps` int (11) default faucet, `users_in_lobby` (11) default zero,` users_in_passive` int (11) default faucet, `users_in_rooms` int (11) default faucet,` cell` integer (11) default faucet, `state By default 'OK', 'user_drop` int (11) tap default' 0 ',' Repeat 'complete by' enum '(' OK ',' warning '' important ',' unknown ') Key (Titles), Key `staticx` (` state ')); A (8) default zero, primary key (`id`), unique key' key 'jadex' (` when transmitted '), key `title code' = MySam AUTO_INCREMENT = 120512615 DEFAULT Cheraset = UTF8;    
 
 
  SELECT t. * From ngr_titles Join T INNER ngr_monitordata m ONET id = m.titleid and m.when_posted = (select MAX (when sent) ngr_monitordata from WHERE titleid = t.id)   
 prompt The solution, though I wrote it in haste, it should work. If it were mine, then I would change the normal data normally to reduce the overhead to the total function and subquery.

Comments

Post a Comment