PHP and MYSQL query to pull duplicates from multiple columns

Question

I am logging information from a threads page on my forum, in 4 days there has been nearly 400,000 entries and one SQL query is using 12 seconds, i need help to reduce the query run time if possible or use php to calculate the guests views for each thread_id

I have the following mysql Table and Columns

thread_count

count_id thread_id user_id timestamp ip_address

I have manged to get the weekly stats in 0.2 seconds for threads with over 100 views using this query

SELECT thread_id, timestamp, user_id, COUNT(thread_id) AS cnt FROM thread_count GROUP BY thread_id HAVING COUNT( thread_id) >100 AND timestamp > UNIX_TIMESTAMP() - 24 * 3600 * 7 ORDER BY cnt DESC

The problem is when I try to run this query to find out how many guests views there has been

$guestcount = SELECT user_id, thread_id, COUNT(user_id) AS cntg FROM thread_count WHERE user_id =0 AND thread_id = ".$array['thread_id']."

That is run in a while loop from the first query

This second query takes 12.9 seconds to run

This is the while loop from the php code, its vbulletin so might look strange

while ($array = $db->fetch_array($threadsql)){

                $thread_title = $db->fetch_array($vbulletin->db->query_read("SELECT title FROM thread WHERE threadid = ".$array['thread_id'].""));

            //  $guestcount = $db->fetch_array($vbulletin->db->query_read("SELECT user_id, thread_id, COUNT(user_id) AS cntg FROM " . TABLE_PREFIX . "thread_count WHERE user_id =0 AND thread_id = ".$array['thread_id'].""));

                $weekly .= '<tr><td><a href="showthread.php?t='.$array['thread_id'].'">'.$array['thread_id'].'</a></td>
                <td>'.$array['cnt'].' </td> <td><a href="showthread.php?t='.$array['thread_id'].'">'.$thread_title[title].'</a></td><td>Username Here</td> <td>'.$guestcount[cntg].'</td> </tr>';               

            }

Basically guests hits are stored as 0 in the user_id column, I did try this and not use the 12 second query but it just shows me 0

foreach($array as $guest){
                if ($array[user_id] = 0 && $thread[thread_id] = $array[thread_id])
                            $guestcount = count($guest);
                }

How can I reduce the second query (where I highlighted the word PROBLEM — Mark Davis
– Mark Davis, Commented Mar 10, 2017 at 19:51

Parfait · Accepted Answer · 2017-03-11 14:09:41Z

2

Consider an SQL solution. Specifically, since you run two different aggregations, use derived tables to return thread_id, cnt, title, and cntg fields all in one query call with nested looping.

Also, unfortunately, like some MySQL developers you are not running valid ANSI SQL as GROUP BY must include all non-aggregated columns and hence your original queries should fail. Likely, you have MySQL's ONLY_FULL_GROUP_BY mode off (defaults to on as of version 5.7.5). Below queries are adjusted to be fully compliant SQL, portable to other RDBMS's.

SELECT t.thread_id, t.cnt, h.title, g.cntg

FROM
  (SELECT c.thread_id, COUNT(c.thread_id) AS cnt 
   FROM thread_count c
   WHERE c.timestamp > UNIX_TIMESTAMP() - 24 * 3600 * 7 
   GROUP BY c.thread_id 
   HAVING COUNT(c.thread_id) > 100
  ) As t 

INNER JOIN    
 (SELECT c.thread_id, COUNT(c.user_id) AS cntg 
  FROM thread_count c
  WHERE c.user_id=0 
  GROUP BY c.thread_id
 ) As g
ON t.thread_id = g.thread_id

INNER JOIN 
  thread h
ON h.threadid = t.thread_id

ORDER BY t.cnt DESC

edited Mar 11, 2017 at 14:09

answered Mar 10, 2017 at 22:32

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Davis Over a year ago

This is brilliant thank you so much. I am looking to order it by cnt I tried> ORDER BY cnt DESC after As t but it gave me MySQL error

Parfait Over a year ago

Place the ORDER BY in outer query at very end. See edit.

Collectives™ on Stack Overflow

PHP and MYSQL query to pull duplicates from multiple columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related