-1

In the app I build users will be able to book appointmnets...on (a specific) page load I must display all appts-past and future-a query does that...each row holds the details of an appointment. Each appt though has also some services and/or staff associated with it...1 or 2 queries will bring this info.

So I must loop for all the appts and run 1/2 queries each time-for every app. So for 20 apts that is 20/40 SELECT queries

From which number of apts onwards you think I will face a perfomace issue? I do not think 20 or 30 appts will create a problem. I know that to give an answer on this info must be given but I do not know which exact info is needed...i can provide though...

Possible workourounds(for which i want your feedback)

1.Display only a specific number of appts on page load(for example 10)...when the user scrolls to the 10th an ajax request is made to bring another 10...maybe the ajax request could be triggered on some 'more' button click or jusn triggered by the scroll event per ce...loop through the appts will be done again but only for these 10 appts I think this is a virtual scroll scenario..but i do not know this is proper solution for this case.

What do you think?Any other possible workarounds. I am just trying to figure out the alogorithm for it. Probably it is sth trivial for today's web standards...for me though sth new.

thx...

P.S we must also consider the UX factor also for a case as the above

here is the fiddle with the info you requested... fiddle

at line 131 you will find the query for getting the appointmnets from the DB

at line 145 is the query to get the services associated w the appt. I did not write the query for the staff cause it is identical to the one with the services.

So..what do you think?

3
  • create a copy of your tables and add some sample data that illustrates the problem. Use for example dbfiddle.uk and update your question with the URL for the fiddle.
    – Lennart
    Dec 1 '21 at 18:00
  • give some time to prepare the fiddle..thanks Dec 1 '21 at 18:18
  • "So I must loop for all the appts and run 1/2 queries each time-for every app." - Why not just run your query once selecting all the information for all appointments that you need to display on that page?
    – J.D.
    Dec 1 '21 at 19:38
1

Try not to explicitly loop. Instead, try to write a JOIN that fetches the appt(s) in the same query. That is likely to be much faster.

SELECT p.*, a.*
    FROM Patients AS p
    JOIN Appts AS a  ON a.patient_id = p.id
    WHERE patient_id = ...
    ORDER BY a.appt_date DESC;

That will give you one row of data for each appointment for the one patient.

Index

To speed up the query at line 131 add this to appointments:

INDEX(apps_origin, delete_front, bookfromID, bookedfor)
3
  • as I said each appt is accompanied by services(which can be more than one) and/or staff..so the above query is not the solution Dec 2 '21 at 13:36
  • 1
    For performance, having optimal indexes is the first move. See what I added.
    – Rick James
    Dec 2 '21 at 17:42
  • yeah..that is part of the solution Dec 3 '21 at 11:31
0

I am going to combine the staff query and services query in one query and add indexes..than I am going to bring only 20-30 appt at a time..I think this must be OK.. at the end of of the 20th or 30th appt I will display a "more" text..upon clicked by the user an ajax request will bring another 20-30 and so on...

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.