0

I'm working on a survey application that has an Android App and a Dashboard as well. We have hosted our code on our own server in Xampp and using MySQL database that is within Xampp. From this week we have observed that MySQL queries are taking a long time even this SELECT COUNT(*) FROM survey_data WHERE 1 simple query take more than 10 seconds to show the result in phpmyadmin. I searched over the internet and found a command that can show the locked table/s. And also show processlist that lists the processes. I looked into it and found that one of my update queries was too heavy so I commented out that for now.
Then I observed that the count query that returns different count values to different users is being repeated again and again. We have about 45 users of Dashboard and that count query returns the values when they refresh the Dashboard But we have more than 16k Android users that submit data (5K to 15k records daily). below is the snapshot that shows more count query requests than the insert and update that should be way larger in amount than the Dashboard hits. enter image description here

You can see there are more count processes than the insert and update (in the picture there is only 1 insert) and also count queries are taking a long time.

There should not be more count queries than insert and update.

Can you please let me know how to reduce the count query time. The queries in phpmyadmin take a long time too So I suppose I have to make some changes in the config files etc. to make it work. I already changed localhost to 127.0.0.1 in httpd.conf file.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Inzimam Tariq IT
  • 6,548
  • 8
  • 42
  • 69
  • _Nothing to do with this probelms but_ It also looks like your dashboard runs using the `root` user account! Not a good idea. Nothing should run using `root` other than jobs that have to be `root` – RiggsFolly Jul 23 '20 at 09:01
  • Can you cache the select queries and display the result from cache? This will save a lot of bandwidth from the server for other essential queries – Chilarai Jul 23 '20 at 09:02
  • @RiggsFolly we don't have any username password on database for now. That's why its root I think, I will change that. – Inzimam Tariq IT Jul 23 '20 at 09:07
  • @Chilarai Dashboard reflects the survey status from the database. With cache would not it show the previously-stored counts? Can you please elaborate a bit. – Inzimam Tariq IT Jul 23 '20 at 09:10
  • 1
    [This Q&A set](https://stackoverflow.com/questions/10976328/mysql-count-performance-on-very-big-tables) might make very useful reading for you – RiggsFolly Jul 23 '20 at 09:28

0 Answers0