php and database, too much query issue

[Pawan_Agarwal]

in database, I have faced error where query does not responded properly

 

it was showing the error message that "too much query, user cannot execute more query"....

 

when I increased the limit, it works for sometime and after sometime, it again generates the error , my question here is:

 

"why the error is occurring ?"

 

"what is the correct way to improve the execution of multiple queries at single instance ??? "

[mac_gyver]

posting the actual error message (and where it is occurring at - php code, database log files...), what limit you changed, what the limit was, and what you changed it to, would help.

[Pawan_Agarwal]

 Warning: mysql_connect() [function.mysql-connect]: User 'admini' has exceeded the 'max_connections_per_hour' resource (current value: 999) in C:\xampp\htdocs\page\verify_total_count.php on line 12

Could not connect: User 'admini' has exceeded the 'max_connections_per_hour' resource (current value: 999)

[mac_gyver]

999 connections per hour is not very many. that would be 16 per minute or one about every 4 seconds.

 

is your web site receiving that many page requests or is your code making multiple database connections each time it runs?

 

you need to find out what your recent peak page requests per hour are and multiply that by at least five to come up with the minimum database connections per hour.

[Pawan_Agarwal]

yes, my website is 16 queries on a single page, what I am providing to user is to count 16 parameters with one click 

 

I am transferring data with AJAX and it provides the total count of all 

 

Suppose, you have 8 types of fruits and 8 types of electronic gadgets and you just want to know the total number of count of each item, so I am doing that..........we have already discussed the error message in the above section

 

Suppose the user clicks too often on the button, and it will execute the page again and again, what is the best way to design the page so that I face no problem in fetching the record from database......

 

what can the maximum value that can be set inplace of 999 ???

 

Suppose I have more that 100,000 user on the website and about 20%-25% are using the website at a time and they want to know the total number of fruits and electronic gadgets available at the store and they are clicking on the button, not it is clear between you and me that 16 queries are going to be executed at one time by more that 2000 people, will the database handle the flow or it will provide some error ??  Is there comes role of bandwidth as the website will be going to post on the web ??

[bsmither]

In the application I am very familiar with, it uses a cache. It will MD5 the query to use as a filename, serialize/base64_encode the resultset as the file contents, then save it.

 

Assuming the database contents don't change all that frequently, the code that would send this query to the database can MD5 the query, do a file_exists() test, and if it's in the cache, re-use that resultset.

Edited May 18, 2014 by bsmither

[Pawan_Agarwal]

the query has to be executed again and again, there is no fix duration in which database has to remain static , it will be updated at any point of time and 16-20 queries has to be executed in my website

 

I understand the point to display the static data using a file, but, what I want to do is to provide functionality to user to alter the choice and fetch database as much as he want , that;s why I am saying that one user will be using 16-20 database related queries at a time, so, when I host the website, will it cause some problem with server or not !!

[Psycho]

First off, there is no reason to need to run 16 queries to get the counts of 16 different "fruit" or "electronics". Assuming these two types of objects need two different tables, you can get the counts you want with ONE query.

 

Second, if your host is limiting you to 999 connections per hour you will need to implement some type of caching to work around this problem - or get a new host. But, fixing the code to use only one query would alleviate the problem considerably.

[bsmither]

" 8 types of fruits and 8 types of electronic gadgets and you just want to know the total number of count of each item"

 

Can we assume that for each of the 16 types/categories of items, they can be grouped by a distinct identifier? Such that:

SELECT COUNT(*) AS `count` FROM `table` WHERE `condition` = 'x'

SELECT COUNT(*) AS `count` FROM `table` WHERE `condition` = 'y'

SELECT COUNT(*) AS `count` FROM `table` WHERE `condition` = 'z'

 

Can this be grouped such as:

SELECT COUNT(`condition`) FROM `table` GROUP BY `condition`

(Hope I got that right.)

[Pawan_Agarwal]

Hi bsmither, 

You have guessed the right problem that I am facing. There are 8 different fruits and 8 different gadgets. when user click on button then with the help of javascript. it shows the count for all the 16 things.. it is causing higher flow of database query..I want to count each of them...on one button click, 16 query get executed and display count infront of each item like 

 

apple : x

banana : y

cherry : z

pine-apple : z1 and this has to be completed  for all 16 items