How to optimize queries?

[eevan79]

How to reduce queries with php?

I'm working on script where is about 42 queries on index page, and max 10 queries on topic and "read-posts" page.

I put simple counter where is mysql_fetch_assoc and on index page there are 2 while loops - for categories and posts. Result is 42 queries (if I count subcategories, 15 queries if not). I think thats a lot.

 

Here is structure of my index page:

First I select all from categories (I need them all) - ID, Name, Description, Icon, Parent, Child and JOIN LEFT topics table.

 

In while loop I render html tables - one row for each category.

If I have enables to display subcategories I have in same loop another while loop (before html output) where I select all categories table where cat_parent field >0 (that means if there are sub-categories) .

 

And finally I have last while loop where are html output for topics in category ID.

 

Result is 15 queries without enabled option to display sub-categories and 42 with that option. And thats result for 6 categories with 5 sub-categories.

I can not imagine what would happen if there are more categories and sub.

 

How to reduce this queries? There are problem with mysql_fetch_assoc where is few queries per category. More categories more queries.

 

How SMF have only few queries with page where is about 30 topics?

[ChemicalBliss]

Sounds like your going a very long way around selecting what you want from the database.

 

Using JOINS you should only need one query to get all topics with names/dates of posts and everything related. In fact you could get the categories while your at it too.

 

FYI, you can use almost limitless JOINS in a single query.

 

We will need an example to get a better picture, explain your index page with some code and actual queries.

-cb-

[premiso]

For category pulling, there is no real "clean" way to do it. The only thing you can do is "cache" the categories in a few different ways.

 

A: Pull them once at user's first view of the site and store them in session. The issue here is that if you update a category, the user's data is potentially out dated, so you would need to figure out a check, maybe a hash in a "status" table of category_last_updated.  If it is not this hash then re-pull the data.

 

B: Similar to method A but store the categories in a "cache" file in a serialized array / object. Then just update this file when you add a category and it should keep the user up to date automagically.

 

I am not sure how bigger sites do it, but I would imagine some type of a cache in session or in a file with a serialized array. I would choose the "cache" file, imo. Then you would just have to tailor your code to loop through an array recursively. Is this any better, I have no clue. But it sounds pretty fun to try.

[Psycho]

You should almost never, ever need to do queries within loops:

 

In while loop I render html tables - one row for each category.

If I have enables to display subcategories I have in same loop another while loop (before html output) where I select all categories table where cat_parent field >0 (that means if there are sub-categories) .

 

You should start here. Instead of doing sub-queries for each subcategory your first query should return all categories AND all subcategories in one query. Then process the records within PHP to display the categories and subcategories appropriately.

 

Although, from your description I am not understanding this statement:

Result is 15 queries without enabled option...

 

Your statements seem to suggest you are running one query to get the main categories

First I select all from categories (I need them all)...

 

So why do you have 15 queries just to get the parent categories?

[eevan79]

Thanks for reply.

So why do you have 15 queries just to get the parent categories?

For each category and another for topics in that category.

Although, from your description I am not understanding this statement:

Quote<blockquote>Result is 15 queries without enabled option...</blockquote>

Thats option in config.php - subcategories_on_index ...

 

Here is my index.php in attachment. Please note that I am very begginer with php, but learning quickly. ^^

 

EDIT: Screenshot of category table.

 

 

[attachment deleted by admin]

[ChemicalBliss]

I'm not going to look at it right now but just for reference:

A. Use proper indexing on your code. If your lazy you can just run your script through http://www.phpformatter.com/.

B. Never attach code unless you cannot post the script in your topic, people hate downloading stuff for no reason.

 

So, for your index i will help you out, i have not fixed anything or optimized, i have only formatted your code and posted here so others have an easier time helping you.

 

<?php
  include 'header.php';
  $result = mysql_query("SELECT 
	categories.cat_id, 
	categories.cat_name, 
	categories.cat_description, 
	cat_icon, 
	cat_parent, 
	cat_child, 
	COUNT(topics.topic_id) AS " . $table_prefix . "topics 
FROM " . $table_prefix . "categories 
LEFT JOIN " . $table_prefix . "topics ON topics.topic_id = categories.cat_id
GROUP BY categories.cat_id, 
	categories.cat_name, 
	categories.cat_description 
ORDER BY categories.cat_id 
ASC
  ");
  $qqq = 1;
  if (!$result) {
      echo $l_cat_cant_display;
  } else {
      if (mysql_num_rows($result) == 0) {
          echo $l_no_cat;
      } else {
          //prepare the table
          $tags = array('{CATEGORY}', '{LAST_TOPIC}', '{TOPICS}', );
          $data = array($l_category, $l_last_topic, $l_topics, );
          echo str_replace($tags, $data, file_get_contents("./style/" . $default_style . "/categories.html"));
          $pageTitle = $l_forumname;
          while ($row = mysql_fetch_assoc($result)) {
              $qqq += 1;
              if ($row['cat_parent'] == $row['cat_id']) {
                  $subforum = $row['cat_parent'];
                  echo 'a' . $subforum;
              }
              
              //Show only 'root' categories
              if ($row['cat_parent'] < 1) {
                  if ($subforums_on_index == 1) {
                      //CHECK SUBFORUMS
                      $subforum = "";
                      $subforum_lang = "";
                      $result2 = mysql_query("SELECT * FROM " . $table_prefix . "categories WHERE cat_parent >0");
                      $qqq += 1;
                      while ($row2 = mysql_fetch_assoc($result2)) {
                          $qqq += 1;
                          if ($row2['cat_parent'] == $row['cat_id']) {
                              $subforum = $subforum . '<img src="./style/' . $default_style . '/img/subforum_old.gif"> <a class="subforums" href="category.php?f=' . $row2["cat_id"] . '">[' . $row2["cat_name"] . ']</a>    ';
                              $subforum_lang = "<br /> subforums: ";
                          }
                      }
                      if ($row['cat_child'] != 0) {
                          $subforum = $subforum . '<br />';
                      }
                  }
                  $tags = array('{FORUM_IMG}', '{FORUM_ID}', '{FORUM_NAME}', '{FORUM_DESC}', '{SUBFORUM}', '{L_SUBFORUM}');
                  $data = array('<img src="./style/' . $default_style . '/img/' . $row['cat_icon'] . '">', $row['cat_id'], $row['cat_name'], $row['cat_description'], $subforum, $subforum_lang);
                  echo str_replace($tags, $data, file_get_contents("./style/" . $default_style . "/categories_row.html"));
                  //fetch last topic for each cat
                  $topicsresult = mysql_query("SELECT * FROM " . $table_prefix . "topics WHERE topic_cat = " . $row['cat_id'] . " ORDER BY topic_last_post_date DESC LIMIT 0,1");
                  
                  if (!$topicsresult) {
                      echo $l_last_topic_error;
                  } else {
                      if (mysql_num_rows($topicsresult) == 0) {
                          echo '<td>  no topics</td>';
                      }
                      
                      else {
                          $result2 = mysql_query("SELECT reply FROM " . $table_prefix . "topics WHERE topic_cat =" . $row['cat_id'] . "") or die(mysql_error());
                          
                          while ($topicrow = mysql_fetch_assoc($topicsresult)) {
                              $qqq += 1;
                              $num_topics = mysql_num_rows($result2);
                              $tags = array('{$T_PATH}', '{FORUM_ID}', '{TOPIC_ID}', '{TOPIC_SUBJECT}', '{TOPIC_LAST_POST}', '{TOPIC_DATE}', '{NUM_TOPICS}', );
                              $data = array('style/' . $default_style . '/img/', $row['cat_id'], $topicrow['topic_id'], $topicrow['topic_subject'], $topicrow['topic_last_poster'], date($date_format, strtotime($topicrow['topic_last_post_date'])), $num_topics, );
                              echo str_replace($tags, $data, file_get_contents("./style/" . $default_style . "/categories_list_topics.html"));
                          }
                      }
                  }
                  echo '</td>';
                  echo '</tr>';
              }
          }
      }
  }
  include 'footer.php';
  // Get all the page's HTML into a string
  $pageContents = ob_get_contents();
  // Wipe the buffer 
  ob_end_clean();
  echo str_replace('<!--TITLE-->', $pageTitle, $pageContents);
?>

 

-cb-

[eevan79]

A. Use proper indexing on your code. If your lazy you can just run your script through http://www.phpformatter.com/.

 

So, for your index i will help you out, i have not fixed anything or optimized, i have only formatted your code and posted here so others have an easier time helping you.

Thanks -cb-. Thats very usefull.

 

A: Pull them once at user's first view of the site and store them in session. The issue here is that if you update a category, the user's data is potentially out dated, so you would need to figure out a check, maybe a hash in a "status" table of category_last_updated.  If it is not this hash then re-pull the data.

 

B: Similar to method A but store the categories in a "cache" file in a serialized array / object. Then just update this file when you add a category and it should keep the user up to date automagically.

Thats nice solution. I must learn how to cache index page . But that will not update last topic for that category.

 

EDIT: I just found usefull link about caching pages (maybe outdated): http://simonwillison.net/2003/May/5/cachingWithPHP/

and than I just need to perform queries for last topic on that category...

[Pikachu2000]

Just curious, what makes you think you need to go through all of this? Are you having performance or lag issues or something?

[eevan79]

Just curious, what makes you think you need to go through all of this? Are you having performance or lag issues or something?

I dont have performance or lag issues. Just want to reduce unnecessary queries. Because if there are (for example) 30 categories with 30 sub-categories there will be more than 100 queries per page load (index) .

 

I like idea for caching categories instead of using mysql queries. What is best way to cache categories into file?

 

For now I use following scipt:

if (file_exists('./cache/'.md5($_SERVER["SERVER_NAME"]).'.php')) {
$var = file('./cache/'.md5($_SERVER["SERVER_NAME"]).'.php');

foreach($var as $k => $v){
echo '<br />'.$k.' is '.htmlentities($v); //...reading categories
}
}

(if there is no file, data will be obtained from the mysql database).

[Psycho]

Before you look into caching you need to first solve the problem of how you are extracting the data.

So why do you have 15 queries just to get the parent categories?

For each category and another for topics in that category.

 

You can get ALL of that data with a single query! I'll take a look at the code and see if I can provide a solution. At the very least I'll provide a method to point you in the right direction. It is difficult sometimes to reverse engineer someones code.

[eevan79]

You can get ALL of that data with a single query! I'll take a look at the code and see if I can provide a solution. At the very least I'll provide a method to point you in the right direction. It is difficult sometimes to reverse engineer someones code.

Thanks.

This serialize method for caching makes me headache . I understand how to write serialized data, but dont realize how to return data in strings. For example, I serialize category, ID, name, parent, child. And dont understand how to pull this data into strings $catID, $catName, $catDescription etc...but thats another topic.

[Psycho]

You can get ALL of that data with a single query! I'll take a look at the code and see if I can provide a solution. At the very least I'll provide a method to point you in the right direction. It is difficult sometimes to reverse engineer someones code.

Thanks.

This serialize method for caching makes me headache . I understand how to write serialized data, but dont realize how to return data in strings. For example, I serialize category, ID, name, parent, child. And dont understand how to pull this data into strings $catID, $catName, $catDescription etc...but thats another topic.

 

I'm not sure where you are going there. I've been slammed today and have not had time to work on this. This is really more of a MySQL problem and not a PHP problem. Here is the MySQL reference section that is at the heart of the issue: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

 

For example, you can either select only the parent categories and join the table on itself to get the assciated sub categories or you could just do a query for all the categories and subcategories creating a custom value to use for ordering the records. But the real difficulty is selecting the most recent topic along with each category in a single query. That is what the link above is about.

 

Here is a query that will get you the parent categories, subcategories and the most recent topic post for each. Because I don't have your DB to test against I can't say there are no errors. But, hopefully it will work. Category/subcategory records should be ordered like this:

 

Category 1

Sub Category 1-A

Sub Category 1-B

Sub Category 1-C

Category 2

Sub Category 2-A

Sub Category 2-B

 

  $query = "SELECT c.cat_id, c.cat_name, c.cat_description, c.cat_icon, c.cat_parent, c.cat_child,
                   IF(c.cat_parent=0, c.cat_id, c.cat_parent) as firstOrderBy,
                   t.topic_id, t.topic_subject, t.topic_last_poster, t.topic_last_post_date,
                   COUNT(t.topic_id) AS topic_count
            FROM {$table_prefix}.categories c
            LEFT JOIN {$table_prefix}.topics t
                   ON t.topic_id = (SELECT topic_id
                                    FROM {$table_prefix}.topics
                                    WHERE t.topic_cat = c.cat_id
                                    ORDER BY topic_last_post_date DESC
                                    LIMIT 0,1)
            GROUP BY c.cat_id, c.cat_name, c.cat_description
            ORDER BY firstOrderBy ASC, c.cat_parent ASC";

[eevan79]

if(!$query)

{echo $l_cat_cant_display;}

Thanks mjdamato.

 

This is "echo" after mysql_query: The categories could not be displayed, please try again later.

 

But you point me in right direction. I read all articles about LEFT JOIN but still dont have enough expirience to use this queries with many conditions.

 

Now I realize that this is topic for mysql section.

 

Anyway, with perfect query SELECT I'll get queries as much as categories when fetching data. That's not big deal, but just wonder how scripts like phpbb or smf have max 5-10 queries per page?

I looked into source and as I can see solution is in caching data. Thats impossible to get 5 queries where is about 30 categories and 20 subcategories at one page (with all data about users, topics,categories, sub-categories, dates)...or I maybe wrong? 

[eevan79]

This issue is more related to MySql. Can moderator move this topic there?

 

By the way, here is example of script - http://avs-demo.iz.rs/testforum/

 

I optimized script now and get less queries than before. Only on index page I get more than 20 queries.