A long query ... would you have done it better?

[darwin2kx]

I am using a long query with several subs to get a chunk of data from two tables. I look at it, and it looks long and complicated though. Anyone out there have any suggestions on how to improve this? If you need more info, just ask and it shall be provided.

 

SELECT count( pn.pokernightid ) AS games, t.tournamentid, t.tournamentname, (

SELECT count( * ) AS count
FROM pokernights pn
WHERE pn.pokernightdate >1219158259
AND pn.tournamentid = t.tournamentid
) AS upcoming, (

SELECT pn.pokernightdate
FROM pokernights pn
WHERE pn.pokernightdate >1219158259
AND pn.tournamentid = t.tournamentid
ORDER BY pn.pokernightdate ASC
LIMIT 0 , 1
) AS nextnight
FROM tournaments t
LEFT JOIN pokernights pn ON t.tournamentid = pn.tournamentid
WHERE t.memberid = '1'
GROUP BY pn.tournamentid 

 

To get this result:

games 	tournamentid 	tournamentname 		upcoming		nextnight
0 	2 		Test Tournament 		0 		NULL
2 	1 		Cory's Tournament - Test 	1 		1220050800

[cooldude832]

is it slow?

 

does the EXPLAIN have a lot of nulls?

[darwin2kx]

I'm new to the EXPLAIN statement. It appears to be ok, but here is the result.

id 	select_type 		table 	type 	possible_keys 	key 		key_len 	ref 		rows 	Extra
1 	PRIMARY 			t 	ref 	memberid 		memberid 		8 	const 		1 	Using temporary; Using filesort
1 	PRIMARY 			pn 	ref 	tournamentid 	tournamentid 	8 	db.t.tournamentid 	2 	 
3 	DEPENDENT SUBQUERY 	pn 	ref 	tournamentid 	tournamentid 	8 	func 		2 	Using where; Using filesort
2 	DEPENDENT SUBQUERY 	pn 	ref 	tournamentid 	tournamentid 	8 	func 		2 	Using where

 

As for the query time, I have no good baseline yet because there is not enough data to query across yet.

[cooldude832]

It looks decent fenway can really tell you if its good but like you seem to hint at subqueries usually aren't great.

[darwin2kx]

Not that, just overall it seems like a huge query. I'm just asking to see if there is anything that I could combine, or any better way this could be written.

[cooldude832]

maybe try something like this (Gotta work out the exacts just an idea)

 

SELECT 
count( pn.pokernightid ) AS games,
t.tournamentid,
t.tournamentname,
COUNT(T2.*) as upcomming,
T3.pokernightdate as nextnight

FROM tournaments t
LEFT JOIN pokernights pn ON t.tournamentid = pn.tournamentid
LEFT JOIN pokernights as T2 ON(T2.tournamentid = t.tournamentid AND T2.pokernightdate >1219158259)
LEFT JOIN pokernights as T3 ON(T3.tournamentid = t.tournamentid AND T3.pokernightdate >1219158259)

WHERE t.memberid = '1'

GROUP BY pn.tournamentid 

[darwin2kx]

Results in a 29% speed improvement over the old one. Only problem is it doesn't return the right upcoming count.

 

SELECT
count( pn.pokernightid ) AS games,
t.tournamentid,
t.tournamentname,
COUNT(T2.pokernightid) as upcoming,
T3.pokernightdate as nextnight	
FROM tournaments t
LEFT JOIN pokernights pn ON t.tournamentid = pn.tournamentid
LEFT JOIN pokernights as T2 ON(T2.tournamentid = t.tournamentid AND T2.pokernightdate > 1219158259)
LEFT JOIN pokernights as T3 ON(T3.tournamentid = t.tournamentid AND T3.pokernightdate > 1219158259)
WHERE t.memberid = '1'
GROUP BY pn.tournamentid

[cooldude832]

you may have to just subquery it  or subquery on the JOIN sorta like

SELECT
count( pn.pokernightid ) AS games,
t.tournamentid,
t.tournamentname,
T2.upcomming as upcoming,
T3.pokernightdate as nextnight	
FROM tournaments t
LEFT JOIN pokernights pn ON t.tournamentid = pn.tournamentid
LEFT JOIN (Select COUNT(*) as upcomming from `pokernights` 
	Where tournamentid = t.tournamentid pokernightdate > 1219158259) as T2 ON(1)
LEFT JOIN pokernights as T3 ON(T3.tournamentid = t.tournamentid AND T3.pokernightdate > 1219158259)
WHERE t.memberid = '1'
GROUP BY pn.tournamentid

[darwin2kx]

SELECT
count( pn.pokernightid ) AS games,
t.tournamentid,
t.tournamentname,
T2.upcoming as upcoming,
T3.pokernightdate as nextnight	
FROM tournaments t
LEFT JOIN pokernights pn ON t.tournamentid = pn.tournamentid
LEFT JOIN (Select COUNT(*) as upcoming from `pokernights` 
	Where tournamentid = t.tournamentid AND pokernightdate > 1219158259) as T2 ON(1)
LEFT JOIN pokernights as T3 ON(T3.tournamentid = t.tournamentid AND T3.pokernightdate > 1219158259)
WHERE t.memberid = '1'
GROUP BY pn.tournamentid

Throws a "#1054 - Unknown column 't.tournamentid' in 'where clause' ". Not exactly sure at this point what goes where to fix that XD

[cooldude832]

try

SELECT
count( pn.pokernightid ) AS games,
t.tournamentid,
t.tournamentname,
T2.upcoming as upcoming,
T3.pokernightdate as nextnight	
FROM tournaments t
LEFT JOIN pokernights pn ON t.tournamentid = pn.tournamentid
LEFT JOIN (Select COUNT(*) as upcoming from `pokernights` 
	Where  pokernightdate > 1219158259) as T2 ON(T2.tournamentid = t.tournamentid)
LEFT JOIN pokernights as T3 ON(T3.tournamentid = t.tournamentid AND T3.pokernightdate > 1219158259)
WHERE t.memberid = '1'
GROUP BY pn.tournamentid

[darwin2kx]

Still the wrong column error.

[fenway]

Two dependent subqueries? Ouch...

[darwin2kx]

As I said, I'm new and looking for any advice you could offer to make it better.

[fenway]

As I said, I'm new and looking for any advice you could offer to make it better.

Then could you explain what you're trying to do, and post the table structures?

[darwin2kx]

I know that there are a few fields that I could chop the length down on, but that sort of tuning comes after this. What I am trying to achieve is getting the data in my first post, with the query in my first post. However, I would like to make the query itself more efficient if it is possible to do.

 

CREATE TABLE IF NOT EXISTS `tournaments` (
  `memberid` bigint(10) NOT NULL,
  `tournamentid` bigint(10) NOT NULL auto_increment,
  `tournamentname` varchar(255) NOT NULL,
  `entryfee` varchar(20) NOT NULL default '0.00',
  `standingchip` varchar(255) NOT NULL default '0',
  `rebuys` varchar(255) NOT NULL default '1/0/0.00/1',
  `addons` varchar(255) NOT NULL default '0/1,1/0.00/0',
  `badgamedrop` varchar(100) NOT NULL default '2',
  `mingamesplayed` varchar(100) NOT NULL default '3',
  PRIMARY KEY  (`tournamentid`),
  KEY `memberid` (`memberid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `tournaments` (`memberid`, `tournamentid`, `tournamentname`, `entryfee`, `standingchip`, `rebuys`, `addons`, `chipvalues`, `badgamedrop`, `mingamesplayed`) VALUES(1, 1, 'Cory''s Tournament - Test', '0.00', '1500', '2/0/0.00/1', '0/1,1/0.00/0', '2', '3');
INSERT INTO `tournaments` (`memberid`, `tournamentid`, `tournamentname`, `entryfee`, `standingchip`, `rebuys`, `addons`, `chipvalues`, `badgamedrop`, `mingamesplayed`) VALUES(1, 2, 'Test Tournament', '0.00', '0', '1/0/0.00/1', '0/1,1/0.00/0', '2', '3');


CREATE TABLE IF NOT EXISTS `pokernights` (
  `memberid` bigint(10) NOT NULL,
  `pokernightid` bigint(10) NOT NULL auto_increment,
  `pokernightdesc` varchar(255) NOT NULL,
  `pokernightdate` varchar(255) NOT NULL default '0',
  `tournamentid` bigint(10) NOT NULL,
  `entryfee` varchar(255) NOT NULL default '0.00',
  `addedby` varchar(255) NOT NULL,
  `locationid` bigint(10) NOT NULL,
  `maxplayers` varchar(20) NOT NULL default '8',
  `maxplayerspertable` varchar(20) NOT NULL default '8',
  PRIMARY KEY  (`pokernightid`),
  KEY `memberid` (`memberid`),
  KEY `tournamentid` (`tournamentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `pokernights` (`memberid`, `pokernightid`, `pokernightdesc`, `pokernightdate`, `tournamentid`, `entryfee`, `addedby`, `locationid`, `maxplayers`, `maxplayerspertable`) VALUES(1, 1, 'Cory\\''s Test Game', '1218236400', 1, '20.00', '', 1, '16', '8');
INSERT INTO `pokernights` (`memberid`, `pokernightid`, `pokernightdesc`, `pokernightdate`, `tournamentid`, `entryfee`, `addedby`, `locationid`, `maxplayers`, `maxplayerspertable`) VALUES(1, 2, 'Upcoming Game Test', '1220050800', 1, '25.00', '', 1, '16', '8');
INSERT INTO `pokernights` (`memberid`, `pokernightid`, `pokernightdesc`, `pokernightdate`, `tournamentid`, `entryfee`, `addedby`, `locationid`, `maxplayers`, `maxplayerspertable`) VALUES(1, 3, 'Test Game #1', '1219446000', 2, '25.00', '', 2, '10', '10');

[fenway]

What I am trying to achieve is getting the data in my first post, with the query in my first post. However, I would like to make the query itself more efficient if it is possible to do.

Yes, I understand -- so could you explain this in plain English?