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

MySQLoptimizationperformancePHPquery-performance

I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted).

Furthermore I'd like to show a list of users voted, similar to fb (for now my solution is to get that list with new query fetched by ajax call.)

would it be better to create one query to get all results and read them separatelly from array or is it best practice to create separate requests?

My query bellow does get the ordered list but includes the empty or null rows and is very slow for table with >10000 results. any suggestions?

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

Code to match results of votes with voters:

SELECT DISTINCT fbid, COALESCE(username, 'User not found.') 
AS username 
FROM tbl_votes JOIN tbl_users 
ON tbl_users.fbid = tbl_votes.user_id 
WHERE pollid = $band_id AND not isnull(username)

Best Answer

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.

Related Question