MYSQL Query Optimization with subqueries and joins

Question

Hi I have 8 million row data and need to optimize Mysql query to fetch a row from that data. I am using below query but its server response time is too high that creating issue in page loading speed

SELECT q.id
     , q.title
     , q.question
     , q.price
     , q.created
     , q.userid
     , q.duedate
     , q.tags
     , s.id subjectid
     , sc.id subcategoryid
     , s.title subject
     , sc.title subcategory
     , q.statusid
     , (SELECT  COUNT(id) FROM tbl_answers a WHERE a.questionid = q.id AND a.statusid = 1 AND a.deleted = 'N') q_num_answers
     , u.username
     , u.image
     , u.gender
     , (SELECT  COUNT(id) FROM tbl_answers a WHERE a.userid = q.userid AND a.statusid = 1 AND a.deleted = 'N') num_answers
     , (SELECT  COUNT(id) FROM tbl_questions WHERE userid = q.userid AND statusid = 1 AND deleted = 'N') AS num_questions
     , 0 amt_earned
     , 0 amt_spent
     , 0 num_sold
     , (SELECT  COUNT(ur.id) FROM tbl_users_ratings ur WHERE ur.userid = q.userid AND ur.deleted = 'N') u_num_ratings
     , (SELECT  COALESCE(SUM(ur.rating), 0) FROM tbl_users_ratings ur WHERE ur.userid = q.userid AND ur.deleted = 'N') u_score
  FROM tbl_questions q
  JOIN tbl_subjects s 
    ON q.subject = s.id
  JOIN tbl_subjects sc 
    ON q.subcategory = sc.id
  LEFT 
  JOIN tbl_users u 
    ON u.id = q.userid
 WHERE q.deleted = '$show_deleted' 
   AND q.id = ? 
 LIMIT 1

Note that LIMIT without ORDER BY is fairly meaningless. If it was me, I'd see [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) and start over. — Strawberry, Jan 24 '20 at 11:52
Also, you're open to SQL injection, but I would hope that you're aware of that already. — Strawberry, Jan 24 '20 at 11:52
Oh, and note that (as well as SHOW CREATE TABLE statements for all relevant tables) questions about query optimization always require the EXPLAIN for the given query. — Strawberry, Jan 24 '20 at 11:59
@Strawberry Limit without order by isn't meaningless--it returns that many rows. It's just not what they want if it's not what they want. — philipxy, Jan 24 '20 at 22:08
@Strawberry and philipxy thanks for you help. I am aware of SQL injection and other things I just need help to optimize my sql query so it take less time in execution. — Inderjeet Singh, Jan 27 '20 at 05:25
Again: Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) [ask] For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. For SQL performance that includes EXPLAIN results & statistics. (Constraints, indexes & plans are critical.) — philipxy, Jan 31 '20 at 00:35

score 0 · Answer 1 · answered Jan 30 '20 at 05:41

These indexes may help. (I am assuming that id is the PRIMARY KEY of each table.)

ur:  (deleted, userid, rating)
a:  (deleted, statusid, userid)
a:  (deleted, statusid, questionid)

Please provide EXPLAIN SELECT ....

Don't use COUNT(id) unless you need to check for id not being NULL. The usual way to write it is COUNT(*).

In one place you are checking for a provided values for deleted. In another, you hard code it. Perhaps wrong?

AND  ur.deleted = 'N'

If the PRIMARY KEY for q is id, then this will lead to either 1 row or no row. What am I missing?

    WHERE  q.deleted = '$show_deleted'
      AND  q.id = ?

(There may be more tips. Please change the things I suggested and follow the suggestions from others. Then let's have another look.)

MYSQL Query Optimization with subqueries and joins

1 Answers1