How I can optimize this sql query?

Question

I have some SQL query, but it loads server very strongly because of there can be hundreds of users served by this query:

SELECT *
FROM questions
WHERE date > '$day'
  AND answer!=''
  AND tos IN
   (
     SELECT tos
     FROM subscribers 
     WHERE froms='$myid'
   )
ORDER BY date DESC LIMIT $start, $limit

Start & limit are for pagination there (increasing 10 by 10).

Do you have any indexes? on date and answer column? on froms in the subscribers? Please post the query plan. — StanislavL, Jun 05 '18 at 08:22
You can use "EXISTS" clause instead of "IN" clause. https://stackoverflow.com/a/2065403/3373957 — Yasin, Jun 05 '18 at 08:29
@StanislavL drug moy shas vse obyasnu :D Okay, this is a query for user's feed getting posts of users to whom user have subscribed. Date is used to get only last 20 days' posts, answer column is used to get only replied posts, and froms column is user's personal id to identify to whom user have subscribed. — Scripy, Jun 05 '18 at 08:30

score 0 · Answer 1 · answered Jun 05 '18 at 09:01

You should create indexes in your tables (create relationship where possible). The following JOIN can help you

 SELECT q.*
 FROM questions q
 INNER JOIN subscribers s
 ON q.tos=s.tos
 WHERE q.date > '$day'
   AND q.answer<>''
   AND s.froms='$myid'
 ORDER BY q.date DESC LIMIT $start, $limit

To use EXISTS as suggested

  SELECT *
  FROM questions q
  WHERE date > '$day'
    AND answer!=''
    AND EXISTS 
     (
       SELECT *
       FROM subscribers s
       WHERE s.froms='$myid' AND s.tos=q.tos
     )
  ORDER BY date DESC LIMIT $start, $limit

PS: Because of this dollar sign $ and LIMIT, It sounds as Php MySQL...if yes, please add the two tags to avoid confusion with pure SQL. There's noway to paginate data in pure SQL: it depends on your DBMS

. . Perhaps you have never heard of `FETCH`, but standard SQL does support pagination. — Gordon Linoff, Jun 05 '18 at 10:51

score 0 · Accepted Answer · answered Jun 05 '18 at 09:05

0

The query looks good as is. I see nothing I'd change. What you can do is provide indexes:

create index idx1 on subscribers(froms, tos);
create index idx2 on questions(tos, date, answer);
create index idx3 on questions(tos, answer, date);

(I don't know whether idx2 or idx3 is better. Check which one is being used and drop the other.)

As to paging: This leads to selecting all data and sorting it with every call. This can be much slower than simply retrieving it in one go and handling paging in your app or website. (You also get records multiplied in case data is being entered into the table between your calls, and possibly gaps, if somebody is deleting rows.)

answered Jun 05 '18 at 09:05

Thorsten Kettner

89,309
7
49
73

Oh! It's a very good answer, should be choices as an answer! But will that work for multiple users requesting different values for froms, tos, date and answer? – Scripy Jun 05 '18 at 10:23
Yes, why shouldn't it? Every user asks for and gets the data they are interested in. Concurrent reads are no problem for a DBMS. – Thorsten Kettner Jun 05 '18 at 11:07

How I can optimize this sql query?

2 Answers2