0
 $query= "SELECT * FROM games where games.league='".$receivedleague."' AND  games.gamedate >='".$todaydate."' AND
   '".$elnowla."' <=  games.eldatetime  AND   (Not EXISTS(SELECT gameplayed.gameid,gameplayed.userid FROM gameplayed WHERE games.id=gameplayed.gameid AND gameplayed.userid='".$Userid."'))  order by games.eldatetime asc limit 0,2" or die(mysql_error()) ; 

Hello gurus !

Can anyone help me to improve this query below ? I am no experty in sql.
THe query does display what it is supposed to display, but the subquery takes too long.
Is there another way to make it more efficient ?

$query= "SELECT * FROM games where games.league='".$receivedleague."' AND  games.gamedate >='".$todaydate."' AND
'".$elnowla."' <=  games.eldatetime  AND   (Not EXISTS(SELECT gameplayed.gameid,gameplayed.userid FROM gameplayed WHERE games.id=gameplayed.gameid AND gameplayed.userid='".$Userid."'))  order by games.eldatetime asc limit 0,2" or die(mysql_error()) ; 

Any help would be appreciated. Thanks
Peter

Edited by Reverend Jim: Fixed markdown.

5
Contributors
10
Replies
58
Views
4 Years
Discussion Span
Last Post by Peter_5
0

Hi there and thank you for the reply.
One table (games) contains all game dates of a season .. If a user has played a game it should not show up at the website anymore. Therefore i have this subquery that checks if the game id from the games table was already played. If the game was already played, there will be a record with the game id in the gamesplayed table.
The games table has about 10 000 recods and the gamesplayed table has about 7000 records.
During an active season the gamepayed table can have a lot more records since every game the user plays will be in there.

CREATE TABLE `gameplayed` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `gameid` bigint(20) DEFAULT NULL,
  `origin` varchar(200) DEFAULT NULL,
  `gamedate` date DEFAULT NULL,
  `userid` bigint(20) DEFAULT NULL,
  `userteam` varchar(240) DEFAULT NULL,
  `visitor` varchar(240) DEFAULT NULL,
  `home` varchar(240) DEFAULT NULL,
  `gamelock` int(1) DEFAULT NULL,
  `cutoff` varchar(200) DEFAULT NULL,
  `pickscorevisitor` int(11) DEFAULT NULL,
  `pickscorehome` int(11) DEFAULT NULL,
  `riskpoints` int(11) DEFAULT NULL,
  `winnerpick` varchar(200) DEFAULT NULL,
  `againstthespread` varchar(200) DEFAULT NULL,
  `vagainstthespreadrate` float DEFAULT NULL,
  `hagainstthespreadrate` float DEFAULT NULL,
  `vagainstthespreadpoints` int(11) DEFAULT NULL,
  `bothpickscore` int(11) DEFAULT NULL,
  `hagainstthespreadpoints` int(11) DEFAULT NULL,
  `vpickscorereward` int(11) DEFAULT NULL,
  `hpickscorereward` int(11) DEFAULT NULL,
  `balanced` int(11) DEFAULT NULL,
  `ou` varchar(200) DEFAULT NULL,
  `balanceddate` date DEFAULT NULL,
  `balancedtime` time DEFAULT NULL,
  `vourate` float DEFAULT NULL,
  `gameendpick` varchar(50) DEFAULT NULL,
  `hourate` float DEFAULT NULL,
  `pickwinnervisitorpts` int(11) DEFAULT NULL,
  `pickwinnerhomepts` int(11) DEFAULT NULL,
  `gameendhome` int(11) DEFAULT NULL,
  `gameendvisitor` int(11) DEFAULT NULL,
  `league` varchar(200) DEFAULT NULL,
  `shutouth` char(50) DEFAULT NULL,
  `shutoutv` char(50) DEFAULT NULL,
  `sports` varchar(200) NOT NULL DEFAULT '0',
  `otrewardspts` int(11) NOT NULL DEFAULT '0',
  `balancedby` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14108 DEFAULT CHARSET=latin1;

CREATE TABLE `gameplayed` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `gameid` bigint(20) DEFAULT NULL,
  `origin` varchar(200) DEFAULT NULL,
  `gamedate` date DEFAULT NULL,
  `userid` bigint(20) DEFAULT NULL,
  `userteam` varchar(240) DEFAULT NULL,
  `visitor` varchar(240) DEFAULT NULL,
  `home` varchar(240) DEFAULT NULL,
  `gamelock` int(1) DEFAULT NULL,
  `cutoff` varchar(200) DEFAULT NULL,
  `pickscorevisitor` int(11) DEFAULT NULL,
  `pickscorehome` int(11) DEFAULT NULL,
  `riskpoints` int(11) DEFAULT NULL,
  `winnerpick` varchar(200) DEFAULT NULL,
  `againstthespread` varchar(200) DEFAULT NULL,
  `vagainstthespreadrate` float DEFAULT NULL,
  `hagainstthespreadrate` float DEFAULT NULL,
  `vagainstthespreadpoints` int(11) DEFAULT NULL,
  `bothpickscore` int(11) DEFAULT NULL,
  `hagainstthespreadpoints` int(11) DEFAULT NULL,
  `vpickscorereward` int(11) DEFAULT NULL,
  `hpickscorereward` int(11) DEFAULT NULL,
  `balanced` int(11) DEFAULT NULL,
  `ou` varchar(200) DEFAULT NULL,
  `balanceddate` date DEFAULT NULL,
  `balancedtime` time DEFAULT NULL,
  `vourate` float DEFAULT NULL,
  `gameendpick` varchar(50) DEFAULT NULL,
  `hourate` float DEFAULT NULL,
  `pickwinnervisitorpts` int(11) DEFAULT NULL,
  `pickwinnerhomepts` int(11) DEFAULT NULL,
  `gameendhome` int(11) DEFAULT NULL,
  `gameendvisitor` int(11) DEFAULT NULL,
  `league` varchar(200) DEFAULT NULL,
  `shutouth` char(50) DEFAULT NULL,
  `shutoutv` char(50) DEFAULT NULL,
  `sports` varchar(200) NOT NULL DEFAULT '0',
  `otrewardspts` int(11) NOT NULL DEFAULT '0',
  `balancedby` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14108 DEFAULT CHARSET=latin1;

I hope that helps
best regards,
Peter

Edited by Dani: Formatting fixed

0

Please post your EXPLAIN query like this.

    EXPLAIN SELECT * FROM games where games.league='".$receivedleague."' AND games.gamedate >='".$todaydate."' AND
    '".$elnowla."' <= games.eldatetime AND (Not EXISTS(SELECT gameplayed.gameid,gameplayed.userid FROM gameplayed WHERE games.id=gameplayed.gameid AND gameplayed.userid='".$Userid."')) order by games.eldatetime asc limit 0,2
0

"id"|"select_type"|"table"|"type"|"possible_keys"|"key"|"key_len"|"ref"|"rows"|"Extra"

"1" |"PRIMARY" |"games"|"ALL"|\N|\N|\N|\N |"3114"|"Using where; Using filesort"
"2" |"DEPENDENT SUBQUERY"|"gameplayed"|"ALL"|\N|\N|\N|\N|"4568"|"Using where"

Attachments result.png 13.08 KB
0

Well, there is a correlated subselect between outer select from game and inner select from gameplayed due to WHERE games.id=gameplayed.gameid...

That means for each row of table game the subselct has to be executed. This might slow down performance depending on the number of game's rows. Correlated subselect are commonly considered to be very slow.

There is a rule, I believe first stated by an Oracle DB guru: Each subselect can be replaced by an equivalent but usually more powerful table-join. So try to transform your query containing the correlated subselect into a more efficient (inner) join.

I believe it doesn't matter whether you put extra indexes on further colums because you have already put one on the most important id column.

Edited by 1stDAN

0

Sorry, I was partly wrong when I stated that it wouldn't matter defining a further index!

Because gameid of gameplayed is foreign key that points to id of games it really matters when you put an index on gameid!

I would like to make an suggestion: Today usually innodb is the standard db engine for it its much more better than myisam in supporting SQL, especially the important concept of foreign keys is almost fully implemented in mysql now. (Ok, myisam is said to be much faster (due to missing some sql overhead :))

Edited by 1stDAN

0

Wow ! Thank you all. It has brought the query time down to less than one second. I changed to innodb and put and index on gameid.
Thank you so much
Peter

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.