20 second mysql query... just off test data!

[tibberous]

This query works, but is prohibitively slow:

 

select `id`, `Name`, `Desc`, (select count(*) from `reviews` where `reviews`.`Owner`=`products`.id and `Status`='Approved') as `Reviews`, (select count(*) from `reviews` where `reviews`.`Owner`=`products`.id and `Status`='Pending') as `Pending` from `products` where `Owner`=14 and `Deleted`!=1

 

Basically it counts how many reviews a product has, both approved and pending.

 

Is there a way I can either speed up the query, or a way to store the amount of reviews in the database such that they are kept up to date? Could I setup a mysql trigger on the reviews table that set the Approved and Pending fields of the products table?

[wrathican]

I am no way a mysql pro. or php pro. i dont understand your query. so lets make it a bit simpler.

 

this is how i would do it:

 

table structure

id, product id, name, review, date, status.

 

id: just an auto incremented integer.

product id: the id of the product that should be contained in a separate table.

name: name of person making the review.

review: exactly what it says on the tin.

date: date the review was made.

status: 0 for pending, 1 for approved.

 

firstly i would query the table for all the approved comments

<?php
$prodId = $_GET['productid'];

$approvedQuery = "SELECT * FROM table WHERE status='1' AND product_id='".$prodId."'"; // a status of 1 = approved
$approvedResult = mysql_query($approvedQuery);
$approvedNumRows = mysql_num_rows($approvedResult);

?>

 

then query for the pending reviews

<?php

$pendingQuery = "SELECT * FROM table WHERE status='0' AND product_id='".$prodId."'"; // a status of 0 = pending
$pendingResult = mysql_query($approvedQuery);
$pendingNumRows = mysql_num_rows($approvedResult);

?>

 

then you can echo it as follows:

<?php

$totalReviews = $approvedNumRows + $pendingNumRows;

echo 'This product has a total of '.$totalReviews.' reviews.<br >';
echo $approvedNumRows . ' of these reviews have been approved.<br />';
echo $pendingNumRows . ' of these reviews are awaiting approval.';

?>

 

i hope this helps..

 

[nEJC]

Are there any indexes on review table?

 

If there aren't each result from products table causes 2 full table accesses into review table.

 

There is probably only 1 (default one with primary key in it)

I'd add another index on product_id field. SQL would be something like this:

CREATE INDEX review__product_id ON reviews( product_id );

 

With this each result from products would cause 2 direct accesses into review

 

Hope it helps...

[waynew]

The more simple the SQL query, the quicker it's execution.

[GingerRobot]

The more simple the SQL query, the quicker it's execution.

 

I really don't think anything is as clear-cut as that.

[wrathican]

so does that mean you guys are endorsing my post?

[tibberous]

Are there any indexes on review table?

 

If there aren't each result from products table causes 2 full table accesses into review table.

 

There is probably only 1 (default one with primary key in it)

I'd add another index on product_id field. SQL would be something like this:

CREATE INDEX review__product_id ON reviews( product_id );

 

With this each result from products would cause 2 direct accesses into review

 

Hope it helps...

 

!!! You are a crazy genus!

 

The query time is now .125 seconds.

 

I was going to spend the morning screwing around with mysql triggers - now I am going to spend the morning trying to figure out why what you said to do worked. Thanks a ton - I don't know how many times I might have needed that and just didn't use it... it's like you just showed me the for loop.

 

I don't even have to change any of my code - just boom! FIXED FOREVER!

 

 

[nEJC]

Try reading this ... if you still don't understand I'll try to explain in more simple terms here:

 

Lets say you have a table with only single characters in it like this:

[pre]

char

==

A

B

F

F

A

B

[/pre]

 

this actually looks something like this on a disk:

ABFFAB

 

Now if you do a select like this:

SELECT char FROM mytable WHERE char = 'A';

database would have to read thru all of the data, compare every character with the condition = 'A' (this is also known as full table access)

 

Now if we create index on a table, database would create new table in which it would put the key and where that key can be found, like this:

A -> 1,5

B -> 2,6

F -> 3,4

 

In this case using same SQL would cause database to go only to known positions in table 1 & 5, skipping everything else...

 

There is actually more to indexing, but for a beginner this explanation its sufficient 

 

 

[craygo]

try this out. This is not tested but should give you a general idea

$sql = "SELECT `p`.`id` AS pid, `p`.`Name` AS pname, `p`.`Desc` AS pdesc, COUNT(`r`.`Owner`) AS `Reviews`, COUNT(`pen`.`Owner`) AS `Pending`
        FROM `products` AS `p`
        LEFT JOIN `reviews` AS `r` ON `p`.`id` = `r`.`Owner`
        LEFT JOIN `reviews` AS `pen` ON `p`.`id` = `pen`.`Owner`
        WHERE `r`.`Owner` = '14' AND `r`.`Deleted` != '1'
        GROUP BY `pid`";

 

Ray