Too slow query How can it be improved ?

Question

Peter_5 0 Newbie Poster

11 Years Ago

 $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

mysql

Edited 11 Years Ago by Reverend Jim because: Fixed markdown.

5 Contributors
10 Replies
225 Views
1 Day Discussion Span
Latest Post 11 Years Ago Latest Post by Peter_5

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

Are you using any indexes?

pritaeas 2,194 ¯\_(ツ)_/¯

11 Years Ago

Can you show your table structure? And an EXPLAIN of your query?

Szabi Zsoldos 26 Learner and helper guy

11 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Peter_5 0 Newbie Poster · Answer 1 · 2013-08-09T10:47:56+00:00

hello !

Yes the tables are indexed in the id fields

Peter_5 0 Newbie Poster · Answer 2 · 2013-08-09T12:13:50+00:00

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

score 0 · Answer 3 · 2013-08-09T14:56:56+00:00

Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

11 Years Ago

Can you also post the DDL to create the games table?

Peter_5 0 Newbie Poster · Answer 4 · 2013-08-09T15:07:02+00:00

"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"

1stDAN · Answer 5 · 2013-08-09T16:44:51+00:00

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.

1stDAN · Answer 6 · 2013-08-09T18:19:46+00:00

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 :))

Peter_5 0 Newbie Poster · Answer 7 · 2013-08-10T00:53:14+00:00

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