MySQL difficult query

Question

i delete my previous question to make it easier to understand. I'm developing a forum with this database schema:

ID:INT->PRIMARY-KEY->AUTO_INCREMENT
IDTOPIC:INT (0 if it is the "father" topic OR father's ID if it's a reply)
IDUSER:INT (ID of user who posted)
CONTENT:MEDIUMTEXT
DATE:TIMESTAMP

I need to make a query ordered by date, where to get only last thread reply (not if "i am the last user who replied", IDUSER<>$userid) or father thread topic if there are no replies. Even topic or reply results, i need to print first thread IDTOPIC/IDUSER

My ideal results should be like this:

ID:IDTOPIC:IDUSER:CONTENT:DATE:IDLASTREPLY:IDLASTUSERREPLY:LASTCONTENTREPLY:LASTDATEREPLY

there is no problem if the last four fields are NULL. I need the fastest query possible. Please help me!

You're storing posts using an "adjacency list", which may not be the best structure for you data. You should probably research alternative ways of storing hierarchical data in a database. [This question](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree) is a good start. — eggyal, Apr 25 '12 at 17:19
but my way to make forum is exactly the same of some other "famous" products, like joomla/phpBB... they are all using a single table with a field IDTOPIC or PARENT to split topics from replies... — Giuseppe Donato, Apr 25 '12 at 18:38

tosin · Accepted Answer · 2012-04-26T06:32:44.923

1

SELECT fa.ID, fa.IDTOPIC, fa.IDUSER, fa.CONTENT, fa.DATE,
  re.ID as IDLASTREPLY,
  re.IDUSER as IDLASTUSERREPLY,
  re.CONTENT as LASTCONTENTREPLY,
  re.DATE as LASTDATEREPLY,
  CASE WHEN (re.DATE is NULL) THEN fa.DATE ELSE re.DATE END as LASTUPDATE
FROM post fa
  LEFT JOIN post re ON re.ID =
    (SELECT ID FROM post WHERE IDTOPIC = fa.ID ORDER BY DATE DESC LIMIT 1)
WHERE fa.IDTOPIC = 0
ORDER BY LASTUPDATE DESC

and ORDER as you like.
But it's not so good performance. I think you could improve your table structure.

EDIT: add LASTUPDATE for ORDER

edited Apr 26 '12 at 06:32

answered Apr 25 '12 at 17:57

tosin

1,159
7
14

Thank you, but in this way i could not ORDER because in the case there are no replies, re.DATE is null and that would be the first record displayed. – Giuseppe Donato Apr 25 '12 at 18:27
No, because the right order is: CASE WHEN (re.DATE is NULL) THEN fa.DATE ELSE re.DATE END as LASTUPDATE – Giuseppe Donato Apr 25 '12 at 22:18
Anyway, really thank you! I passed from 0.5/0.6 seconds to 0.04/0.05! Not so bad! ;-) – Giuseppe Donato Apr 25 '12 at 22:21
Oops, this is typo.. of course there should. I collect my code. Anyway, glad working – tosin Apr 26 '12 at 06:32
Anyway, after a lot of thread this select become slow... i need to "shrink" it... – Giuseppe Donato May 03 '12 at 17:26

score 0 · Answer 2 · answered May 09 '12 at 23:15

Unfortunately, this select is not so good after some posts... Today, with a total of about 900 records on the table, when i'm filtering threads by my user id (only 45 distinct threads!), MySQL needs 0.30 sec (at the begin, there were needed only 0.04/0.05 sec... the deterioration i think is proportional to how many threads i subscribed) This could means that when i will have 300 distinct threads subscribed i should wait about 2 seconds for this query... it's too much. The strange thing is if i "limit 0,10" this query or get full query, the execution speed does not change! This is why i think it's not good... because i suppose it have to select the whole data even if i limit. No way to solve. This is how i setup query. There is a new notications table, because i want to use it as notification query (to show ONLY replies not made by $USERID)

NOTIFICATIONS TABLE FIELDS: id,idcontent,userid

SELECT 
    CASE WHEN (re.id is NULL) THEN fa.id ELSE re.id END AS id, 
    fa.id as idtopic, 
    CASE WHEN (re.userid is NULL) THEN fa.userid ELSE re.userid END AS userid, 
    CASE WHEN (re.content is NULL) THEN fa.content ELSE re.content END AS content, 
    n.notify, 
    u.id as reuserid, u.name, u.surname, u.photo, 
    CASE WHEN (re.date is NULL) THEN unix_timestamp(fa.date) ELSE unix_timestamp(re.date) END AS LASTUPDATE 
FROM notifications AS n 
LEFT JOIN post AS fa 
    ON fa.id = n.idcontent 
LEFT JOIN post AS re 
    ON re.id=(SELECT ID FROM post WHERE IDTOPIC = fa.ID AND userid <> $USERID ORDER BY DATE DESC LIMIT 1) 
LEFT JOIN users AS u 
    ON u.id = ( CASE WHEN (re.userid is NULL) THEN fa.userid ELSE re.userid END ) 
WHERE 
    n.userid = $USERID 
    AND NOT (fa.userid = $USERID AND re.userid = $USERID)

MySQL difficult query

2 Answers2