Optimize this SQL!

[Emancipator]

Was going to post this on my other thread, but it was getting off topic. So here goes. How would you optimize this sql statement to help prevent connection issues. I typically have 2 records per page like this.


mysql_select_db($database_vanEE, $vanEE);
$query_movie_categories = "SELECT * FROM movie_category ORDER BY category ASC";
$movie_categories = mysql_query($query_movie_categories, $vanEE) or die(mysql_error());
$row_movie_categories = mysql_fetch_assoc($movie_categories);
$totalRows_movie_categories = mysql_num_rows($movie_categories);

Thankyou php gurus.

[Westech]

I'm by no means a mySQL expert. The best advice I can give you might be to make a db backup before you start messing with it, and to try posting in the database forums on Sitepoint, as there are some very knowledgeable people there.

Also, are your pages loading slowly, or are you just getting occasional messages about too many connections? If it's just the connections message, your server may be able to handle the load as is. Your admin may be able to solve the problem just by increasing the number of connections allowed and/or changing the persistent connections setting.

[r2d2]

I think we need a bit more info. Do you need all the info from each row? What are you doing with the data?

But yes, theres some clever people at Sitepoint, they could probably give you an excellent answer very quick.

[Emancipator]

all of the info in that sql statement is required. And yeah its to many connections error, so i am stream lining my code to make it load faster as well.

[AndyH]

Is "category" indexed in your database?

Not much you can can really do ... I am not sure why that query could even be slow, how many rows in the table?

[freekrai]

First thing I see without seeing more code, is this:


mysql_select_db($database_vanEE, $vanEE);

This should be done only once and that's when you do your initial connection to the db.

Also, make sure you're not doing more than one DB connection per session.

That query's not the problem, it's elsewhere in the code dude.

Are you doing a mysql_connect or mysql_pconnect? This is an important detail because pconnect will keep the connection open.

One thing you can do if you use mysql_connect, then as the very last piece of code you call do this:


mysql_close($vanEE);

This will close the connection, but it's only good if you don't use pconnect.

Sorry I can't help much more without looking deeper but that's what I got from those few lines.

[Emancipator]

your help is much appreciated. I am currently once again overloaded :) Even with my server set to allow 1000 connections. WAY to many readers.

I am using the mysql close, and will make sure im only opening the DB connection once.

Does it use up less if i only look for CERTAIN items instead of generically using the *, ie get everything in the database, instead of one or two fields.

Trying to tweak :)

[r2d2]

I have read that it is best to only get the fields you need to reduce the load. Im sure Freekrai could say for sure, but it does make sense.

[freekrai]

hehe, got the email that r2d2 had replied before I finished hitting reply :)

here's what I was in the process of saying:

Well, the connection message wouldn't be caused by the query. That's from the connections to the db.

It's suggested that you only grab what you need from a query, if you don't need something, don't get it. More optimal and efficient in the long run.

[Emancipator]

ok i am rewriting my code as we speak on some of my pages to resolve the issue. The problem is mainly on my phpBB board where i currently have 190people on at once hammering the hell out of it.

I will post my updated code in a minute. you can pick it apart, and thanks r2 and freekrai.. always nice to pick a few sharp minds.

[Westech]

I have no firsthand experience with this, but I've heard from many different people that vBulletin is much more efficient than phpBB. With a site the size of yours it might be worth investing in vBulletin.

[Emancipator]

yeah i can go the route of vbulletin once i start making a buck or two :) So long as its no problem porting over my phpBB users and data.

So here is my REVISED code on my main index page, if freekrai or R2 or anyone else has any ideas on what else I can refine. I did a fair bit of refinement, Since I just overloaded the site again with 190+ on the boards. The more connections i can clean up the better, and the cleaner my code the better

Here be ye code. and all sqls are closed in the end of my page :)



<?php
//************************************************** ****************************
// Connects to Database and makes connection to the vanEE database!
//************************************************** ****************************
require_once('Connections/vanEE.php');
mysql_select_db($database_vanEE, $vanEE);

?>
<?php
//************************************************** ****************************
// Code that displays last 8 Movies added to database
//************************************************** ****************************
$maxRows_last_movies = 8;
$pageNum_last_movies = 0;
if (isset($_GET['pageNum_last_movies'])) {
$pageNum_last_movies = $_GET['pageNum_last_movies'];
}
$startRow_last_movies = $pageNum_last_movies * $maxRows_last_movies;
$maxRows_last_movies = 8;;
$pageNum_last_movies = 0;
if (isset($_GET['pageNum_last_movies'])) {
$pageNum_last_movies = $_GET['pageNum_last_movies'];
}
$startRow_last_movies = $pageNum_last_movies * $maxRows_last_movies;
$query_last_movies = "SELECT id, movie_name, movie_bio FROM movie_name ORDER BY id DESC";
$query_limit_last_movies = sprintf("%s LIMIT %d, %d", $query_last_movies, $startRow_last_movies, $maxRows_last_movies);
$last_movies = mysql_query($query_limit_last_movies, $vanEE) or die(mysql_error());
$row_last_movies = mysql_fetch_assoc($last_movies);

if (isset($_GET['totalRows_last_movies'])) {
$totalRows_last_movies = $_GET['totalRows_last_movies'];
} else {
$all_last_movies = mysql_query($query_last_movies);
$totalRows_last_movies = mysql_num_rows($all_last_movies);
}
$totalPages_last_movies = ceil($totalRows_last_movies/$maxRows_last_movies)-1;
?>
<?php
//************************************************** ****************************
// Code that displays last 40 news items.
//************************************************** ****************************
$maxRows_movie_news = 40;
$pageNum_movie_news = 0;
if (isset($_GET['pageNum_movie_news'])) {
$pageNum_movie_news = $_GET['pageNum_movie_news'];
}
$startRow_movie_news = $pageNum_movie_news * $maxRows_movie_news;
$maxRows_movie_news = 40;;
$pageNum_movie_news = 0;
if (isset($_GET['pageNum_movie_news'])) {
$pageNum_movie_news = $_GET['pageNum_movie_news'];
}
$startRow_movie_news = $pageNum_movie_news * $maxRows_movie_news;
$query_movie_news = "SELECT title, `date`, `desc`, id FROM movie_news ORDER BY id DESC";
$query_limit_movie_news = sprintf("%s LIMIT %d, %d", $query_movie_news, $startRow_movie_news, $maxRows_movie_news);
$movie_news = mysql_query($query_limit_movie_news, $vanEE) or die(mysql_error());
$row_movie_news = mysql_fetch_assoc($movie_news);

if (isset($_GET['totalRows_movie_news'])) {
$totalRows_movie_news = $_GET['totalRows_movie_news'];
} else {
$all_movie_news = mysql_query($query_movie_news);
$totalRows_movie_news = mysql_num_rows($all_movie_news);
}
$totalPages_movie_news = ceil($totalRows_movie_news/$maxRows_movie_news)-1;

//************************************************** ****************************
// Code that displays News Categories on the top of the page!
//************************************************** ****************************
$query_movie_categories = "SELECT category, id FROM movie_category ORDER BY category ASC";
$movie_categories = mysql_query($query_movie_categories, $vanEE) or die(mysql_error());
$row_movie_categories = mysql_fetch_assoc($movie_categories);
$totalRows_movie_categories = mysql_num_rows($movie_categories);
?>

<?
//************************************************** ****************************
// Displays random feature movie on mainpage.
//************************************************** ****************************
mysql_select_db($database_vanEE, $vanEE);
$query_random_movie = "SELECT movie_name, movie_bio, id , main_image FROM movie_name WHERE movie_name.main_image != 0 ORDER BY RAND() LIMIT 1";
$random_movie = mysql_query($query_random_movie, $vanEE) or die(mysql_error());
$row_random_movie = mysql_fetch_assoc($random_movie);
$totalRows_random_movie = mysql_num_rows($random_movie);
$picture = $row_random_movie['images'];

$maxRows_events = 5;
$pageNum_events = 0;
if (isset($_GET['pageNum_events'])) {
$pageNum_events = $_GET['pageNum_events'];
}
$startRow_events = $pageNum_events * $maxRows_events;
$query_events = "SELECT * FROM movie_events ORDER BY id DESC";
$query_limit_events = sprintf("%s LIMIT %d, %d", $query_events, $startRow_events, $maxRows_events);
$events = mysql_query($query_limit_events, $vanEE) or die(mysql_error());
$row_events = mysql_fetch_assoc($events);

if (isset($_GET['totalRows_events'])) {
$totalRows_events = $_GET['totalRows_events'];
} else {
$all_events = mysql_query($query_events);
$totalRows_events = mysql_num_rows($all_events);
}
$totalPages_events = ceil($totalRows_events/$maxRows_events)-1;
?>


<?php
//************************************************** ****************************
// connects to forum database to get total members and total posts!
//************************************************** ****************************
require_once('Connections/movies_forum.php');
mysql_select_db($database_movies_forum, $movies_forum);
?>

<?php
//************************************************** ****************************
// Displays total posts and the total number of members!
//************************************************** ****************************
$query_Recordset1 = "SELECT post_id FROM phpbb_posts";
$Recordset1 = mysql_query($query_Recordset1, $movies_forum) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

$query_Recordset2 = "SELECT user_id FROM phpbb_users";
$Recordset2 = mysql_query($query_Recordset2, $movies_forum) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>

[AndyH]

Instead of doing the connection to the forum database every page load how about you set a CRON job on a script for 4 times a day which gets the stats you need and puts it into your $vanEE database.

How important is that information and how dynamic is it - not very. It is definatly not worth another database connection.

Do you mind showing us the tables structres of all the tables related to the queries above? Also, in phpMyAdmin find the "propose structure" link on the table structure page (just under all the columns) and look at that.

[alien]

A faster count would be:
$total = mysql_result(mysql_query("SELECT COUNT(id) FROM table"),0);

mysql_close is not usually neccesary as it will be closed automatically at the end of script.

[Emancipator]

ok so my server can still not handle the capacity, is there some sort of easy caching i could put on my page to stop this overloading?

[freekrai]

lots of caching scripts out there.
http://www.websitepublisher.net/article/phpcache/ is a useful article on that.

[Emancipator]

yeah i started using jpcache seems to work well.