Query Help

[TheSentenceGame]

Hello, I'm wondering if someone can lend a hand.  I have a database for comments that stores the origin-page-name (which has a .php page appended with GET variables) and then each page that has comments on it just queries the database for all comments posted from that page.  It's a very simple solution, but it works quite well and quick -- but now I would like to add an index page -- a forum type of layout.

 

I would like the SQL query to get me the:

substring of the origin-page-name (the GET variable part of the pagename),

the last user to submit to that page,

the datetime the last user submitted to that page,

and the total number of comments on that page.

 

Here's what I've tried:

 

select substring(frompage,56) as pagename, username, datetime as lastdate, thecount from comments,
(
select count(*) as thecount, max(datetime) as maxdatetime from comments where frompage like '%pagename.php%' group by frompage
) as c
where frompage like '%pagename.php%' && comments.datetime=c.maxdatetime order by lastdate desc;

 

This works -- problem is, it's slow.  Is there any way to say to mySQL, "while you're doing the GROUP BY and finding the max, also grab other columns in the row that the max is found"?

 

The subquery finds the rows I want in order to perform the MAX(), but then there's no way (that I know of) to also grab the username and datetime from that same row.  Is there a way to do this I haven't figured out?

 

Any help you guys can provide would be extremely appreciated!

 

Thanks - and stop by thesentencegame.com if you want to see the page.

[TheSentenceGame]

Sorry - reposting - forgot to add some useful info -- this time should have everything necessary.  (I was too late to add an update to the previous post)

 

-------------

 

Hello, I'm wondering if someone can lend a hand.  I have a database for comments that stores the origin-page-name (which has a .php page appended with GET variables) and then each page that has comments on it just queries the database for all comments posted from that page.  It's a very simple solution, but it works quite well and quick -- but now I would like to add an index page -- a forum type of layout.

 

I would like the SQL query to get me the:

substring of the origin-page-name (the GET variable part of the pagename),

the last user to submit to that page,

the datetime the last user submitted to that page,

and the total number of comments on that page.

 

 

Here's a sample sub-entry in the table:

+---------+----------------------------------------------------+-------+-------+-----------------+

| c_index | frompage                              | datetime                  | username      | comments                                                                            |

+---------+----------------------------------------------------+-------+-------+-----------------+

|      1  | /path/to/page.php?subpage=xxx | 2005-11-28 23:31:38 | MyUsername | MyComments

+---------+----------------------------------------------------+-------+-------+-----------------+

 

Here's what I've tried:

 

select substring(frompage,56) as pagename, username, datetime as lastdate, thecount from comments,
(
select count(*) as thecount, max(datetime) as maxdatetime from comments where frompage like '%pagename.php%' group by frompage
) as c
where frompage like '%pagename.php%' && comments.datetime=c.maxdatetime order by lastdate desc;

 

here's the "explain" output

+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------+

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

+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------+

|  1 | PRIMARY    | <derived2> | ALL  | NULL          | NULL    |    NULL | NULL |  189 | Using temporary; Using filesort |

|  1 | PRIMARY    | comments  | index | NULL          | PRIMARY |    278 | NULL | 7057 | Using where; Using index        |

|  2 | DERIVED    | comments  | index | NULL          | PRIMARY |    278 | NULL | 7057 | Using where; Using index        |

+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------+

3 rows in set (0.01 sec)

 

The primary index is:  frompage,datetime,username

 

Version info:

mysql  Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3

 

This works -- problem is, it's slow.  I've also tried the same using a join - takes the same amount of time.  Is there any way to say to mySQL, "while you're doing the GROUP BY and finding the max, also grab other columns in the row that the max is found"?

 

The subquery finds the rows I want in order to perform the MAX(), but then there's no way (that I know of) to also grab the username and datetime from that same row.  Is there a way to do this I haven't figured out?

 

Any help you guys can provide would be extremely appreciated!

 

Thanks - and stop by thesentencegame.com if you want to see the page.

 

[fenway]

Not sure (a) why you don't have a key on the join column and (b) why you're running that subquery for _each_ record found.

[TheSentenceGame]

Sorry, not sure if I follow.  I'm still new to sub-queries and optimizations in general.  Would you be willing to elaborate?

[fenway]

It's strange that there are no possible keys to be used... and it looks like the subquery will return the same results regardless.