3

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?

  • Add results of EXPLAIN for your original query. Are the tables MyISAM or InnoDB? Show structure of the tables (SHOW CREATE TABLE <tablename>;) It is not possible to execute that query without a temp table because of the ordering. The rewritten query will probably do the same steps too. – jkavalik Oct 19 '15 at 20:01
  • Mysql 5.1... Seriously? – naschoff Oct 19 '15 at 20:12
  • I have updated the post with the EXPLAIN query. @Naschoff do you think upgrading might fix this issue? I have read bug reports on left join queries with millions of rows. – Steve Payne Oct 20 '15 at 1:41
  • @StevePayne, without a doubt it will help you on many fronts. We're on MariaDB 10.1 now. Not only feature wise but, performance and stability wise it is comparing a Lada to an Audi RS4. Upgrading to anything from MySQL 5.6 and up will be a breath of fresh air for you. Believe me. The query optimizers have made leaps and bounds. – naschoff Oct 20 '15 at 4:47
  • Thank you @naschoff I'm kindove new to having my own server and think I will make this a priority to make sure all programs are up to date. I will upgrade the MYSQL in the next few days and report here (I would do it now, but I don't have time if there is some problem). Thanks!!! – Steve Payne Oct 20 '15 at 14:55
1

This composite index will speed things up:

INDEX(ip, sid, time)

What table is clicks in?

If 1444411110 is a typical time, why is it a BIGINT?

Don't say both of these:

WHERE ID = ... -- toss this
USING (ID)     -- keep this

That is probably what is causing the query to hang.

| improve this answer | |
  • Thank you Rick James! I will look into INDEX to speed up the request. I think I changed it to BIGINT when I ran into problems with a 64 big timestamp. What do you suggest it being? – Steve Payne Oct 23 '15 at 2:59
  • 1444411110 fits fine in a 4-byte INT UNSIGNED. (BIGINT takes 8 bytes.) Do you recall where you came across "a 64 big timestmap"? – Rick James Oct 23 '15 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.