...

View Full Version : Multiple Selects pulling in my blog - im concerned this will run really slowly



chidge
03-20-2009, 11:04 AM
Hi Guys and Gals.

I am new to the world of MYSQL (what a subtly big world it is) and have spent a little time making a blog and reading some good books. I have my blog up and running on my dev machine and everything is working fine and I am now in the process of profiling and benchmarking and tweaking everything as I am concerned how it’s going to run when live and how it will handle increased traffic. I also want to learn about all of this.

I wanted to post an excerpt from the page that pulls individual posts from the Database. My concern is that I have 7 individual select statements performing the following (in order):

- Article id
- Get article, title, author, postdate, image folder, main image, caption, comment status
- Other images and captions
- YouTube video
- Quotes
- Comments count
- Comments and comment replies

The last 5 tables all optionally have data in (I.E you can have quotes or not have quotes), the only fields that are there for every blog are parts of the first two:

- Article id
- Get article, title, author, postdate, comment status

So pretty much that is why I have done them in different statements.

I wanted to ask (and not be spoon fed) what people think to this? Weather it’s an abomination and I should shoot myself and go and stack shelves somewhere or if I am on the right path but need a pointer or two.

My site is expecting reasonable traffic and I will be implementing a PHP caching system on the blog but I obviously want the MYSQL to work as well as it can before I start caching.

Anyway as always thanks in advance for any advice and time spent on this and any experienced pointers and helpful bytes of MYSQL wisdom would be gratefully accepted. And here is the code excerpt:



<?php
$OK = false;

//get the id of the entry from the title for the other calls
$get_id = "SELECT journal_id
FROM journal
WHERE ythd = ?";

$blg_ident = $conn->stmt_init();
if ($blg_ident->prepare($get_id)) {
$blg_ident->bind_param('s', $journal_title);
$blg_ident->bind_result($id);
$OK = $blg_ident->execute();
$blg_ident->fetch();
}
$blg_ident->free_result();

if(isset($blg_ident) && !$OK){
echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
}



//get the main image and the article and title etc
$sql_individual = "SELECT DATE_FORMAT(posted, '%D of %M %Y at %l:%i %p ') AS posted, com_value, DATE_FORMAT(posted, '%y_%m_%d') AS com_date,
IF(fld IS NULL, 'none', fld) AS fld,
IFNULL(writer, CONCAT_WS(' ', (SELECT fname FROM admin WHERE user = crtor), (SELECT lname FROM admin WHERE user = crtor))) AS writer,
ythd, blog, file, cap
FROM journal
JOIN journal_article ON journal.journal_id = journal_article.blg_ident
LEFT JOIN jnl_main_image ON journal.journal_id = jnl_main_image.blg_ident
WHERE journalstatus = 2
AND journal_id = $id";


$ind_result = $conn->query($sql_individual);
$ind_numRows = $ind_result->num_rows;

$ind_result->free_result;

if (!$ind_result){
echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
}



//get other Images if they are there
$oth_img = array();

$OK = false;

$getOtherImages = 'SELECT file, cap, fld
FROM journal_other_image, journal
WHERE blg_ident = ?
AND journal_id = ?
ORDER BY otr_img_id ASC';

$stmt = $conn->stmt_init();

if ($stmt->prepare($getOtherImages)) {

$stmt->bind_param('ii', $id, $id);
$OK = $stmt->execute();
$stmt->bind_result($oth_filenm, $oth_cap, $rtn_fld);

while ($stmt->fetch()) {

$oth_img[] = array('cap' => $oth_cap,
'file' => $oth_filenm);
}

$stmt->free_result();

ksort($oth_img, SORT_NUMERIC);
}
if (isset($stmt) && !$OK){
echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
}




//get YouTube Video
$OK = false;

$getYouTube = 'SELECT ythd, ytthb, ytcm, writer, yturl
FROM jnl_youtube
WHERE blg_ident = ?';

$stmt = $conn->stmt_init();

if ($stmt->prepare($getYouTube)) {

$stmt->bind_param('i', $id);
$OK = $stmt->execute();
$stmt->bind_result($youtube_title, $youtube_thm, $youtube_cm, $youtube_au, $youtube_url);
$stmt->store_result();
$stmt->fetch();

$stmt->free_result();
}
if (isset($stmt) && !$OK){
echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
}



//get Quotes
$OK = false;

$getQuotes = 'SELECT qte
FROM journal_quotes
WHERE blg_ident = ?
ORDER BY qts_id ASC';

$stmt = $conn->stmt_init();

if ($stmt->prepare($getQuotes)) {

$stmt->bind_param('i', $id);
$OK = $stmt->execute();
$stmt->bind_result($return_quote);
while ($stmt->fetch()) {
$quotearray[] = $return_quote;
}
$stmt->free_result();
$stmt->close();
}
if (isset($stmt) && !$OK){
echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
}



//get total amount of comments to create page links (so only 10 comments shown per page and others split accross pages)
$getTotal = "SELECT COUNT(*)
FROM journal_comments
WHERE blg_ident = $id";


$total = $conn->query($getTotal);
$row = $total->fetch_row();
$totalComments = $row[0];

// set the current page
$curPage = isset($_GET['pg']) ? $_GET['pg'] : 0;
$mx = isset($_GET['mx']) ? $_GET['mx'] : 10;
$blog_title = isset($_GET['ythd']) ? $_GET['ythd'] : '';

// calculate the start row of the subset
$stRw = $curPage * $mx;



//get all the comments and all the replys
$OK = false;

$getComments = "SELECT poster,
cmts_id,
IF(site IS NULL, 'none', site) AS site,
journal_comments.cmt,
journal_comments.wrt,
DATE_FORMAT(journal_comments.wrt, '%d/%m/%y %l:%i%p') AS posted,
journal_cmt_rpl.cmt AS rpld,
DATE_FORMAT(journal_cmt_rpl.wrt, '%d/%m/%y %l:%i%p') AS rpldtm
FROM journal_comments
LEFT JOIN journal_cmt_rpl ON journal_comments.cmts_id = journal_cmt_rpl.rpy_t
WHERE journal_comments.blg_ident = $id
AND apvd = 1
ORDER BY wrt ASC
LIMIT $stRw, $mx";

$resultComs = $conn->query($getComments);
$numRowsComs = $resultComs->num_rows;

$resultComs->free_result;

if (!$resultComs){
echo "<div class='errors_db_member red'><img src='../images/exclamation.png' alt='' class='exclamation'/> Problem speaking with database</div>";
}

?>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum