Jump to content

Very slow retrieval of data from a database - what can you do?

0xfo7d

By 0xfo7d
in PHP Coding Help

 Share

Recommended Posts

0xfo7d Newbie

0xfo7d

Posted
Posted
Hello, 

First, 

Says the system that I'm a programmer based in PHP. 

The system is intended to manage children's registration forms, daycare network. 

At the moment there are 11,470 records in a table called rishum. 

I want to pull out the statistics form:

OEQv5a.jpg

Take a few minutes to this page costs. 

Code for retrieving data is:




<center>

<br />

<table border=0 cellspacing=1 cellpadding=0 width="1000px">

<tr>

<td align='right' valign='middle' style='width: 40%;text-align: right;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>שם המוסד</td>

<td align='right' valign='middle' style='width: 15%;text-align: right;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>רישום לקייטנה</td>

<td align='right' valign='middle' style='width: 15%;text-align: right;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>משלמים לקייטנה</td>

<td align='center' valign='middle' style='width: 10%;text-align: center;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>רישום לצהרון</td>

<td align='center' valign='middle' style='width: 10%;text-align: center;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>משלמים לצהרון</td>

<td align='center' valign='middle' style='width: 5%;text-align: center;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>רשומים לבית ספר</td>

<td align='center' valign='middle' style='width: 5%;text-align: center;padding: 5px;background: #061B2A;color: #FFF;font-weight: bold;'>רשומים לגן</td>

</tr>

</table>

<?

 

?>

 

<?php

$res1 = mysql_query("SELECT * FROM `places` ORDER BY `name` desc");

while($row1 = mysql_fetch_array($res1)) {

 

$res_ci = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `city`='".$row1["id"]."' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row_ci = mysql_num_rows($res_ci);

 

$res_ci_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `city`='".$row1["id"]."' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row_ci_PAY = mysql_num_rows($res_ci_PAY);

 

$res2_ci = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `city`='".$row1["id"]."' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row2_ci = mysql_num_rows($res2_ci);

 

$res2_ci_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `city`='".$row1["id"]."' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row2_ci_PAY = mysql_num_rows($res2_ci_PAY);

 

$res_countPerCity_school = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `city`='".$row1["id"]."' AND `soog_mosad`='school' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row_countPerCity_school = mysql_num_rows($res_countPerCity_school);

 

$res_countPerCity_gan = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `city`='".$row1["id"]."' AND `soog_mosad`='gan' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row_countPerCity_gan = mysql_num_rows($res_countPerCity_gan);

 

 

if(($row_ci+$row2_ci) > 0) {

?>

<div class="module" style="width: 1000px"><div class="module_name" align='center' style='cursor: hand;cursor: pointer;'>

<table border=0 cellspacing=1 cellpadding=0 width="1000px">

<tr class="try">

<td align="right" style="text-align: right;width: 40%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row1["name"];?></td>

<td align="center" style="text-align: center;width: 15%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row_ci;?></td>

<td align="center" style="text-align: center;width: 15%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row_ci_PAY;?></td>

<td align="center" style="text-align: center;width: 10%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row2_ci;?></td>

<td align="center" style="text-align: center;width: 10%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row2_ci_PAY;?></td>

<td align="center" style="text-align: center;width: 5%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row_countPerCity_school;?></td>

<td align="center" style="text-align: center;width: 5%;padding: 5px;color: #FFF;font-weight: bold;"><?=$row_countPerCity_gan;?></td>

</tr>

</table>

</div><div class="module_links2" align='center'>

 

<table border=0 cellspacing=1 cellpadding=0 width="1000px">

<?

$resCITY = mysql_query("SELECT * FROM `schools` WHERE `city`='".$row1["id"]."' ORDER BY `type` desc,`name`");

while($rowCITY = mysql_fetch_array($resCITY)) {

 

$res = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `the_mosad`='".$rowCITY["id"]."' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row = mysql_num_rows($res);

 

$res_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `the_mosad`='".$rowCITY["id"]."' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row_PAY = mysql_num_rows($res_PAY);

 

$res2 = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `the_mosad`='".$rowCITY["id"]."' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row2 = mysql_num_rows($res2);

 

$res2_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `the_mosad`='".$rowCITY["id"]."' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row2_PAY = mysql_num_rows($res2_PAY);

 

 

 

if(($row+$row2) > 0) {

?>

<tr>

<td align="rigth" style="width: 40%;text-align: right;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"><?=$rowCITY["name"];?></td>

<td align="center" style="width: 15%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"><a href="index.php?action=rishum&sa=statistics&year=<?=$filterByYear;?>&city=<?=$row1["id"];?>&school=<?=$rowCITY["id"];?>&tofesType=1&typeee=<?=$rowCITY["type"];?><? if($_GET["aougust_form"]) { echo "&aougust_form=1"; } ?>"><?=$row;?></a></td>

<td align="center" style="width: 15%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"><?=$row_PAY;?></td>

<td align="center" style="width:10%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"><a href="index.php?action=rishum&sa=statistics&year=<?=$filterByYear;?>&city=<?=$row1["id"];?>&school=<?=$rowCITY["id"];?>&tofesType=2&typeee=<?=$rowCITY["type"];?><? if($_GET["aougust_form"]) { echo "&aougust_form=1"; } ?>"><?=$row2;?></a></td>

<td align="center" style="width:10%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"><?=$row2_PAY;?></td>

<td align="center" style="width:5%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"> --- </td>

<td align="center" style="width:5%;text-align: center;padding: 5px;background: #ddd;color: #444444;font-weight: bold;"> --- </td>

</tr>

<?

}

}

?>

</table>

</div></div>

<?

}

}

 

 

$res_all = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row_all = mysql_num_rows($res_all);

 

$res_all_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row_all_PAY = mysql_num_rows($res_all_PAY);

 

$res_all2 = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row_all2 = mysql_num_rows($res_all2);

 

$res_all2_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");

$row_all2_PAY = mysql_num_rows($res_all2_PAY);

 

 

?>

<table border=0 cellspacing=1 cellpadding=0 width="1000px">

<tr>

<td align='right' valign='middle' style='width: 40%;text-align: right;padding: 10px;background: #000;color: #FFF;font-weight: bold;'>סה"כ טפסי רישום</td>

<td align='center' valign='middle' style='width: 15%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'><?=$row_all;?></td>

<td align='center' valign='middle' style='width: 15%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'><?=$row_all_PAY;?></td>

<td align='center' valign='middle' style='width: 10%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'><?=$row_all2;?></td>

<td align='center' valign='middle' style='width: 10%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'><?=$row_all2_PAY;?></td>

<td align='center' valign='middle' style='width: 5%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'> --- </td>

<td align='center' valign='middle' style='width: 5%;text-align: center;padding: 10px;background: #000;color: #FFF;font-weight: bold;'> --- </td>

</tr>

</table>

</center>


Link to comment
Share on other sites
mogosselin Newbie

mogosselin

Posted
Posted

Hello Oxfo7d!

 

Your code is a little bit hard to read because there are no indentation. 

 

But, I've seen that you seems to do SQL queries in a loop:

$res1 = mysql_query("SELECT * FROM `places` ORDER BY `name` desc");
while($row1 = mysql_fetch_array($res1)) {
...
}

So all the queries will inside the while will be run for each places. How many 'places' do you have? Because it looks like you have about 15 queries with sub selects (SELECT * FROM (SELECT ...)) which are not the most performant type of queries you could do. 

 

I think that's mostly your problem. You should try to do less queries and less queries with sub-select. 11.5k rows isn't big at all, it shouldn't even be a problem. 

Link to comment
Share on other sites
Jacques1 Prolific Member

Jacques1

Posted
Posted

The problem is that the entire code is broken beyond repair. Seriously, what on earth are you doing there?

 

Not only do the queries make absolutely no sense. You've also copied and pasted them around like crazy. And why on earth do you download the entire tables only to count the rows? Never heard of COUNT(*)?

 

I'm not surprised at all that this takes several minutes. You've done everything to make it as slow as possible.

 

Unfortunately, this won't be an easy fix. What can I say? Throw away the script, learn the basics of PHP and SQL and start over. Or find a programmer who's willing to write the code for you.

  • Like 1
Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted (edited)

OK guys, no need to be so harsh. We were all beginners at one point.

 

0xfo7d, there are a few specific problems:

 

1) You are not using JOINs to relate your tables in the queries. Being able to JOIN tables in your queries is the real power that a relational database gives you. It can be hard to grasp at first. Take a little time and look at a tutorial or two. Never, ever run queries in loops unless you have no other alternative. And that should be very few scenarios.

 

2) You are using '*' in all the queries even though you don't need all the data.

 

3) You are querying ALL the records only to get the count.

 

I'll look at the queries a little more to see if I can provide some revised ones to get just the data you need with only one or a few queries (with no loops). But, it's difficult without understanding the schema.

 

 

EDIT: I'll add a #4:

 

 

SELECT *
FROM (SELECT *
      FROM `rishum`
      WHERE `status`!='not_relevant'
      AND `city`='".$row1["id"]."'
      AND `rishum_to`='1'
      AND `aougust_form`='".$_GET["aougust_form"]."'
      AND `date`>='".$filterByYear."/04/22'
      AND `date`<='".($filterByYear+1)."/04/21') AS c
GROUP BY `talmid_id`

 

This really makes no sense. Why would you run a sub-query only so you can select all the records and do a GROUP BY? You can simply do the GROUP BY in the inner query and not need to make it a sub-query:

 

SELECT *
FROM `rishum`
WHERE `status`!='not_relevant'
  AND `city`='".$row1["id"]."'
  AND `rishum_to`='1'
  AND `aougust_form`='".$_GET["aougust_form"]."'
  AND `date`>='".$filterByYear."/04/22'
  AND `date`<='".($filterByYear+1)."/04/21'
GROUP BY `talmid_id`
Edited by Psycho
  • Like 1
Link to comment
Share on other sites
Jacques1 Prolific Member

Jacques1

Posted
Posted

You don't use a GROUP BY clause at all. The sole purpose of grouping is to apply an aggregate function like COUNT() or SUM() to specific subsets of the result set. No aggregate function, no grouping.

 

In fact, a proper database system wouldn't even let you run the query. This violates the SQL standard in every possible way. Unfortunately, MySQL accepts almost anything, so even the worst mistakes tend to go unnoticed.

 

Dropping the raw user input (like $_GET["aougust_form"]) into the queries also isn't the best idea. You think nobody would attack a day care site? I wouldn't bet on that.

Link to comment
Share on other sites
phpPeter Newbie

phpPeter

Posted
Posted

First, please clean up your queries and remove these senseless "group by"s. As long, as it is absolutely unclear, what the query should do at all, it is difficult to help you in detail.

 

I will try to understand this:

 

$res_all = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_all = mysql_num_rows($res_all);

 

should mean:
--> SELECT count(*) as row_all FROM rishum WHERE status != "not relevant" and rishum_to=1 and aougust_form=? AND date BETWEEN ? AND ?

 

 
$res_all_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_all_PAY = mysql_num_rows($res_all_PAY);
 

--> SELECT count(*) AS row_all_PAY FROM rishum WHERE status = "payed" and rishum_to=1 and aougust_form=? AND date BETWEEN ? AND ?

 

 

 
$res_all2 = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_all2 = mysql_num_rows($res_all2);
 

--> SELECT count(*) AS row_all2 FROM rishum WHERE status != "not relevant" and rishum_to=2 and aougust_form=? AND date BETWEEN ? AND ?

 

 

$res_all2_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`");
$row_all2_PAY = mysql_num_rows($res_all2_PAY);

 

--> SELECT count(*) as row_all2_PAY FROM rishum WHERE status = "payed" and rishum_to=2 and aougust_form=? AND date BETWEEN ? AND ?

 

 
...
 
So, first, you should rework your queries to my suggestions. This will improve your performance multiple times, because you don't have to transfer all datasets to count them afterwards, but this will do the query engine for you.
However, I still haven't found out, what the "talmid_id" should be good for...
 
 
Then, you can define the right indices. I think, you can leave it away with such a tiny table (the 11k records will be stored in the mem buffer after first query).
But to be accurate, lets define the required index. My suggestion:
 
CREATE INDEX i1 ON rishum(rishum_to, aougust_form, date, status);
 
After that, it should be a matter of milliseconds... ;-)
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.