Page 1 of 1

Multiple Selects pulling in my blog - im concerned this will

Posted: Fri Mar 20, 2009 4:07 am
by chidge
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:

Code: Select all

<?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>";
        }
    
    ?>

Re: Multiple Selects pulling in my blog - im concerned this will

Posted: Fri Mar 20, 2009 4:11 am
by Benjamin
Great job on the code. I wouldn't be concerned about 7 queries at all... you have to retrieve what you have to retrieve. Just be sure your table is indexed correctly and you shouldn't run into any overload issues for a long time.

Re: Multiple Selects pulling in my blog - im concerned this will

Posted: Fri Mar 20, 2009 4:17 am
by chidge
thanks
yes all are indexed and not over indexed, I had a look through with explain a little while ago

Re: Multiple Selects pulling in my blog - im concerned this will

Posted: Fri Mar 20, 2009 10:54 am
by Bill H
I want to add how refreshing it is to find someone who has studied so thoroughly, and is concerned not just with doing something that looks good on the surface, but with doing it well at its functional level.

Your concern re queries is by no means misplaced, but responding to queries what a database does; that is its purpose in life. One does, indeed, want to query the database as efficiently as possible, but there is no need to be bashful about it.

I say that not as criticism, but merely to offer a degree of comfort with a process that, by your own statement, is new to you. You are very much on the right track in your approach.

As a case in point, the average person new to databases doesn't even know what "explain" is.

Re: Multiple Selects pulling in my blog - im concerned this will

Posted: Fri Mar 20, 2009 11:18 am
by chidge
Thanks, it has been part of a huge learning curve which has involved PHP, JavaScript, Xml and MYSQL to name a few! (coming from an Xhtml, CSS, Design and 3D background)
I am setting up as self-employed and have two huge sites nearing completion to launch with I am also in the fortunate position of not having crazy time frames or tight financial limitations (currently living at my parents as my life is on hold). Meaning I can look into everything as much as I like and read the many books as many times as I like (well, within reason!)

For me MYSQL has been the real surprise - it looked simpleish but is rather surprisingly incredibly complex.

I can see I am only just breathing against the Iceberg known as MYSQL

Re: Multiple Selects pulling in my blog - im concerned this will

Posted: Fri Mar 20, 2009 12:30 pm
by chidge
also I have had some great replies from here http://forums.devshed.com/mysql-help-4/ ... 98338.html 7 queries have gone down to 5 and I have replaced many sub queries with left joins