query overuse (200+)

markg85 · Jul 11, 2005

hello,

im currently running pafiledb on my test machine and its really a awsome file database script.. but as soon as i install the 4 level sub mod (its here: [url]http://forums.pamods.net/index.php?showtopic=8[/url] and [url]http://forums.pamods.net/index.php?showtopic=121[/url] for the correct count but with HUGE query counts :( ) im stuck with the problem that i get a huge query count where the subcatogory`s are... this is what i currently have: MySQL Queries Used : 207 Queries

Thats just extreme and i would like to bring it down to a normal level.. i only dont know why and its not my intention to rebuild the entire database mod since thats to manny work.

But i did find out whitch part of the code is causing the high query count.. it`s this "babe"

function get_file_count($db, $pafiledb_sql, $cat_id)
{
 $filesincat = 0;
 if (!$cat_id == "")
 {
   $result = $pafiledb_sql->query($db, "SELECT * FROM $db[prefix]_cat WHERE cat_parent = $cat_id ORDER BY cat_order", 0);
   $filesincat += $pafiledb_sql->query($db, "SELECT * FROM $db[prefix]_files WHERE file_catid = $cat_id", 2);
   while(
   $i = mysql_fetch_row($result)
   )
   {
     $filesincat += get_file_count($db, $pafiledb_sql, $i[0]);
   }
 }
 return $filesincat;
}

"This is an example where the function is being used.. where you see $filesincat is the place where it`s actually being executed.

while ($category = mysql_fetch_object($result)) {
if ($category->cat_parent == 0)
{
$filesincat = get_file_count($db, $pafiledb_sql, $category->cat_id);
	//Smalltext on and off function
	if ($configexed[1] == 0) 
	{
		if ($zz==0)
		{
			echo "<tr><td width=\"80%\" class=\"datacell\"><a href=\"pafiledb.php?action=category&id=$category->cat_id\">$category->cat_name</a></td><td width=\"10%\" class=\"datacell\"><center>$filesincat</center></td></tr>";
			$zz = 1;
		}
		else
		{
			echo "<tr><td width=\"80%\" class=\"datacell2\"><a href=\"pafiledb.php?action=category&id=$category->cat_id\">$category->cat_name</a></td><td width=\"10%\" class=\"datacell2\"><center>$filesincat</center></td></tr>";
			$zz =0;
		}

} 
else 
{
	if ($zz==0)
	{
		echo "<tr><td width=\"80%\" class=\"datacell\"><a href=\"pafiledb.php?action=category&id=$category->cat_id\">$category->cat_name</a><br><a class=\"smalltext\">&raquo; $category->cat_desc &laquo;</a></td><td width=\"10%\" class=\"datacell\"><center>$filesincat</center></td></tr>";
		$zz = 1;
	}
	else
	{
		echo "<tr><td width=\"80%\" class=\"datacell2\"><a href=\"pafiledb.php?action=category&id=$category->cat_id\">$category->cat_name</a><br><a class=\"smalltext\">&raquo; $category->cat_desc &laquo;</a></td><td width=\"10%\" class=\"datacell2\"><center>$filesincat</center></td></tr>";
		$zz = 0;
	}

}
}
}

dont look at the $zz = 0 en $zz = 1.. im changing that for a way more efficient way of doing the 2 colored listing

i hope you guys have all the info you need to give me hints/example how to fix this problem. if not than ask as much as you want

drew010 · Jul 11, 2005

im not really sure if theres a shorter way to do it, but i would personally say its best to either modify the script so that count updates some text files every time a file is uploaded or deleted, or use a cron job to get the counts every hour or something. save the file count to a text file and at the point where its supposed to show up just read the count from file.

markg85 · Jul 11, 2005

well.. there is another way to do it but that means rebuilding the entire menu and sub menu system... that a bit to much for me lol

saving it to a file is not handy because there can be like 100 catogory`s on big sites.. and modding it (like i just said) is to manny work.

drew010 · Jul 11, 2005

just a thought here, but could it be possible to query a list of all categories (or subcategories) and then issue one query for each category like "SELECT COUNT(*) FROM table WHERE category = 'cat'
that way if you have 10 subcategories, it should only total 11 queries, one to get the list, and then 1 count query for each subcategory...

markg85 · Jul 11, 2005

well.. ot`s not easy to do that.. the query table has these collums:

 cat_id
 cat_name
 cat_desc
 cat_files
 cat_1xid
 cat_parent
 cat_order

but some of them aren`t even used ... like CAT FILES!!, cat_1xid(whatever it is..)

drew010 · Jul 11, 2005

SELECT COUNT(*) FROM table WHERE cat_name = 'name' should give a count of the files in the category "name"

markg85 · Jul 11, 2005

just tried it and im getting results like: Resource id #17 and counting up till the end of the catogorys

this is how my full query looks like now:

$filesincat = $pafiledb_sql->query($db, "SELECT COUNT(*) FROM $db[prefix]_cat WHERE cat_name = '$category->cat_name'");

drew010 · Jul 11, 2005

im not sure how their query method works, but normally you have to call a mysql_fetch_* function to get the row that mysql returned.

markg85 · Jul 11, 2005

this is mysql.php you can see there how all query`s will be handled

class pafiledb_sql {
	function query($db,$query,$type) {
		$result = mysql_query($query);
		$error = mysql_error();
		global $query_count;
		global $queries_used;
		$query_count++;
		$queries_used .= "<tr><td width=\"100%\" align=\"left\" class=\"datacell\">$query</td></tr>";
		if (!empty($error)) {
			$errno = mysql_errno();
			$this->error("paFileDB was unable to successfully run a MySQL query.<br>MySQL Returned this error: <b>$error</b> Error number: <b>$errno</b><br>The query that caused this error was: <b>$query</b>");
		}
		if ($type == 0) {
			return $result;
		}
		if ($type == 1) {
			$array = mysql_fetch_array($result);
			return $array;
		}
		if ($type == 2) {
			$array = mysql_num_rows($result);
			return $array;
		}
		if ($type == 3) {
			$array = mysql_insert_id();
			return $array;
		}
		@mysql_free_result($result);
	}
	function error($error) {
		die($error);
	}
	function connect($db) {
		if(!($dbc = mysql_connect($db['host'],$db['user'],$db['pass']))) $this->error("paFileDB was unable to successfully connect to the MySQL database. Check your settings including the MySQL server, username, and password and try again.");
                if(!(mysql_select_db($db['name'],$dbc))) $this->error("paFileDB was able to connect to the MySQL database, but was unable to select the database <b>$db[name]</b> to use.");
	}
}
$pafiledb_sql = new pafiledb_sql;

drew010 · Jul 11, 2005

looks like if you pass a 2 as the $type, you can take the return of the query function and echo $res[0] and get the count

markg85 · Jul 11, 2005

i`m further but now i got just ones every category has 1 file now ....

drew010 · Jul 11, 2005

is there another table where the file information is stored, maybe that one is just for info on categories?

markg85 · Jul 11, 2005

there is no table where the file count is stored if you mean that... and you know the rest

drew010 · Jul 11, 2005

i mean where the information on all the files is stored.

markg85 · Jul 12, 2005

o that information.. i will give it to you

there is a table named pafiledb_cat whitch is the info that you already have and for the files there is a table called pafiledb_files. this is what`s in that table:

 file_id
 file_name
 file_desc
 file_creator
 file_version
 file_longdesc
 file_ssurl
 file_ssurl2
 file_ssurl3
 file_ssurl4
 file_ssurl5
 file_dlurl
 file_size
 file_time
 file_catid
 file_posticon
 file_license
 file_dls
 file_last
 file_pin
 file_docsurl
 file_rating
 file_ratingcount
 file_totalvotes
 file_disable

i hope that`s the info you needed

drew010 · Jul 12, 2005

ok yeah this is what will help. first obtain a list of the category id's with on query on that table, then from this one you can do
SELECT COUNT(*) FROM pafiledb_files WHERE file_catid = 'x'
that will show how many files are in category x

markg85 · Jul 12, 2005

how do i do 2 querys in one row? (thats needed for this)

drew010 · Jul 12, 2005

im not sure i understand that

markg85 · Jul 12, 2005

well.. i got this now as the full line:

$filesincat = $pafiledb_sql->query($db, "SELECT COUNT(*) FROM $db[prefix]_cat WHERE cat_id = '$file[file_catid]'", 2);

and how do i do 2 query`s in that line... is that like this:

$filesincat = $pafiledb_sql->query($db, "SELECT COUNT() FROM $db[prefix]_cat WHERE cat_id = '$file[file_catid]'"; "SELECT COUNT() FROM pafiledb_files WHERE file_catid = 'x' ", 2);

that`s just an example ... it just for you to get the idea what i mean

drew010 · Jul 13, 2005

you should only need the second select count, the first one should always give 1.

query overuse (200+)

Mmarkg85

Ddrew010

Mmarkg85

Ddrew010

Mmarkg85

Ddrew010

Mmarkg85

Ddrew010

Mmarkg85

Ddrew010

Mmarkg85

Ddrew010

Mmarkg85

Ddrew010

Mmarkg85

Ddrew010

Mmarkg85

Ddrew010

Mmarkg85

Ddrew010