MySQL deducing fields dynamically

MySQLoptimizationperformancequery-performance

I'm trying to deduce a value in a select statement, then reuse that calculation a few times per row, as well as group by that value. In my case, I'm doing time_to_sec(timediff(created, lastHit)) a bunch of times:

select
     time_to_sec(timediff(created, lastHit)) diff
    , if(
         time_to_sec(timediff(created, lastHit)) > 1800
         OR time_to_sec(timediff(created, lastHit)) = 0,
         1, 0
      ) newSession
    , sum(1) as sessions
    , sum(
          if(
             time_to_sec(timediff(created, lastHit)) > 1800, 
             0, time_to_sec(timediff(created, lastHit))
          )
      ) as totalDuration
    from pageviews
    group by newSession;

How can I improve that query without storing redundant data in my schema (such as the pre-calculated time difference between created and lastHit)?

I thought about using variables (select @diff := timediff(...)), but this post mentions that I should avoid assigning a variable and reading from it within the same statement.

Best Answer

In most situations, the recalculation of an expression is much less of a performance burden than any solution. So, don't worry about it.

You have another issue -- The GROUP BY does not include the first thing in the SELECT, so the value you will get for that is unpredictable.

Related Solutions

Mysql – show results from 3 tables excluding NULL or EMPTY values for certain fields

Try this:

SELECT id, 
  title, 
  pollid, 
  COUNT(pollid) AS clean_count,
  group_concat(DISTINCT u.name) AS user
FROM 
(
  SELECT DISTINCT user_id, pollid 
  FROM tbl_votes
) AS tmp_tbl 
JOIN tbl_polls 
  ON tbl_polls.id = tmp_tbl.pollid
JOIN tbl_users AS u 
ON tmp_tbl.user_id = u.user_id
WHERE not isnull(tmp_tbl.user_id) 
GROUP BY pollid 
ORDER BY clean_count DESC

This is the sample data that I used:

    CREATE TABLE tbl_polls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(50)
  );


CREATE TABLE tbl_users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
  );

CREATE TABLE tbl_votes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pollid INT,
  user_id INT,
  INDEX (pollid,user_id),
  FOREIGN KEY (pollid) REFERENCES tbl_polls(id),
  FOREIGN KEY (user_id) REFERENCES tbl_users(user_id)
  );

INSERT INTO tbl_polls (title)
VALUES ('Title 1'),('Title 2'),('Title 3');


INSERT INTO tbl_users (name)
VALUES ('Bob'),('Jack'),('Joe'),('Tom');

INSERT INTO tbl_votes (pollid,user_id)
VALUES (1,2),(1,2),(1,2),(1,3),(2,4),(2,1),(2,4),(2,4),(3,1),(2,1),(1,4);

SQL FIDDLE DEMO

I have used group concat to get the names for your list. Furthermore, to speed up your database have you tried adding index to the foreign keys?

B.T.W. I don't understand your comment that some some votes lack pollid and user_id. If that is the case have a look at this.

Mysql – index doesn’t work properly

I am not a MySQL expert (or even a user!) but the ideal index for your query appears to be as shown below:

create table `single` (
  `game_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(9) NOT NULL,
  `stage` tinyint(4) NOT NULL,
  `highscore` bigint(20) unsigned NOT NULL,
  `played_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_id`),
  KEY `user_id_stage` (`stage`,`user_id`, `highscore`)
)

The plan I get is:

Explain

See the SQLfiddle

Best Answer

Related Solutions

Mysql – show results from 3 tables excluding NULL or EMPTY values for certain fields

Mysql – index doesn’t work properly

Related Question