Mysql – SQL Freezing on Temp Table Creation

I have done an update to my server yesterday to allow for tracking of user page views. These page views are sent to a database where it records their ip address, time and which page was viewed.

On the bottom of each of my pages, I now have a complex SQL statement that is determining which pages to show by checking against what they have viewed.

Anyway, now for some reason my SQL is randomly getting stuck at creating a table for this query.

Here is the query:

SELECT 
  * 
FROM posts
  LEFT JOIN impressions ON 
    impressions.ID = posts.ID AND impressions.time > '1444400410' AND (`ip` = '3093454573')
WHERE `usa` = '1'
GROUP BY posts.ID 
ORDER BY 
  COUNT(posts.ID) ASC, clicks DESC

This SQL call normally pulls results in

Showing rows 0 - 24 (202 total, Query took 0.0151 seconds.)

It seems to work fine but will get stuck at creating a temporary table some times. This SQL call is on a main page where it is getting hit by robots, search engines and regular users.

When this get's stuck on creating a temporary table, it is locking all new sql requests until it hits Too Many Connections taking my website offline.

Could this be happening because there may be two views from the same person at the same time?

Should I be locking the table?

Is there a way to do this call without creating a temporary table?

I am really stuck on why this keeps happening and I have tried locking the tables with no success.

Connection id:          124
Current database:       mbs28242
Current user:           mbs@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.73 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 50 sec

Threads: 1  
Questions: 1248  
Slow queries: 0  
Opens: 61  
Flush tables: 1  
Open tables: 54  
Queries per second avg: 24.960

Here is my attempt to re-create this query (hopefully I am on the right path!?!?)

SELECT * FROM posts A LEFT JOIN (
SELECT * FROM `impressions` WHERE ID = impressions.SID AND impressions.time > '1444411110' AND (`ip` = '3093454573')
) b
USING (ID)
WHERE `usa` = '1' GROUP BY ID ORDER BY COUNT(ID) ASC, clicks DESC

Showing rows 0 - 24 (205 total, Query took 0.0320 seconds.)

Uptime:                 1 hour 48 min 25 sec

Looking good so far!!!

First EXPLAIN Query

+----+-------------+---------+-------+----------------------------------------------------------------------------------+--------------+---------+-------------------------------+------+----------------------------------------------+
| id | select_type | table   | type  | possible_keys                                                                    | key          | key_len | ref                           | rows | Extra                                        |
+----+-------------+---------+-------+----------------------------------------------------------------------------------+--------------+---------+-------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | posts  | range | id,post,title,ends,lots of keys                                                | added | 4       | NULL                          | 1518 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | impressions | ref   | MID,FBID,TID,SID                                                                 | ID          | 5       | mbs28242.SID |  280 |                                              |
+----+-------------+---------+-------+----------------------------------------------------------------------------------+--------------+---------+-------------------------------+------+----------------------------------------------+

2 rows in set (0.00 sec)

The new query which seems to be working

+----+-------------+------------+-------------+----------------------------------------------------------------------------------+--------------+---------+------+-------+-----------------------------------------------------+
| id | select_type | table      | type        | possible_keys                                                                    | key          | key_len | ref  | rows  | Extra                                               |
+----+-------------+------------+-------------+----------------------------------------------------------------------------------+--------------+---------+------+-------+-----------------------------------------------------+
|  1 | PRIMARY     | A          | range       | id,post,title,ends,lots of keys                                                | added | 4       | NULL |  1518 | Using where; Using temporary; Using filesort        |
|  1 | PRIMARY     | <derived2> | ALL         | NULL                                                                             | NULL         | NULL    | NULL |    43 |                                                     |
|  2 | DERIVED     | impressions    | index_merge | MID,FBID,TID                                                                     | MID,FBID,TID | 9,9,9   | NULL | 24445 | Using union(MID,FBID,TID); Using where; Using index |
+----+-------------+------------+-------------+----------------------------------------------------------------------------------+--------------+---------+------+-------+-----------------------------------------------------+

3 rows in set (0.03 sec)

This 2nd query, which is longer, has not crashed mysql for over 24 hours. The first query would crash mysql within 10 minutes sometimes.

SHOW CREATE TABLE QUERIES

CREATE TABLE `posts` (
 `SID` int(5) NOT NULL AUTO_INCREMENT,
 `pending` tinyint(4) NOT NULL DEFAULT '1',
 `suggestion` tinyint(4) NOT NULL DEFAULT '0',
 `title` text,
 `description` text,
 `url` text,
 `url2` text,
 `url_image` text NOT NULL,
 `url_image_name` text NOT NULL,
 `url_changed` text NOT NULL,
 `start` date DEFAULT NULL,
 `addedtime` text,
 `updatetime` text,
 `int_views` int(11) NOT NULL DEFAULT '1',
 `int_views2` int(11) NOT NULL DEFAULT '1',
 `int_views2b` int(11) NOT NULL DEFAULT '0',
 `int_clicks` int(11) NOT NULL DEFAULT '1',
 `int_clicks2` int(11) NOT NULL DEFAULT '0',
 `tweetmsg` text NOT NULL,
 `keywords` text NOT NULL,
 `keyword_description` text NOT NULL,
 `sdescription` text NOT NULL,
 `mypicksdescription` text NOT NULL,
 `usa` tinyint(4) NOT NULL DEFAULT '1',
 `ca` tinyint(4) NOT NULL DEFAULT '0',
 `states` text,
 PRIMARY KEY (`SID`),
 FULLTEXT KEY `url` (`url`),
 FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=72243 DEFAULT CHARSET=utf



CREATE TABLE `impressions` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `SID` int(11) DEFAULT NULL,
 `MID` bigint(20) DEFAULT NULL,
 `FBID` bigint(20) DEFAULT NULL,
 `TID` bigint(20) DEFAULT NULL,
 `time` bigint(20) DEFAULT NULL,
 `ip` int(11) unsigned DEFAULT NULL,
 `data` tinyint(4) NOT NULL DEFAULT '0',
 UNIQUE KEY `id` (`id`),
 KEY `SID` (`SID`),
 KEY `MID` (`MID`),
 KEY `FBID` (`FBID`),
 KEY `TID` (`TID`),
 KEY `ip` (`ip`)
) ENGINE=MyISAM AUTO_INCREMENT=256477 DEFAULT CHARSET=utf8

Here is my most recent updated query which is still getting stuck on creating temporary table:

 SELECT *, COUNT(b.SID) as mcount, MAX(b.time) as mtime FROM posts a
 LEFT JOIN impressions b
 ON a.SID = b.SID AND b.time > '1444635223' AND (b.MID = '17395' OR b.FBID = '16467')
 WHERE pending != 1 AND usa = 1 AND end >= '2015-10-22' AND added >= '2015-10-12' AND url_image > '' 
 GROUP BY a.SID
 ORDER BY mcount DESC, (a.int_clicks + (a.int_views2b * 100)) DESC

It seems to be getting "stuck" for certain queries, when there are no matches on the left join for Impressions and the MID or FBID does not match.

When I run the same query with an INNER JOIN, it finds no matches and tells me the results in (Query took 0.0848 seconds.).

It seems to be something with the left join?