query is returning over 74,000 results and taking 30 seconds - HELP!

------=_NextPart_000_002B_01C35A6D.9945F620
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi Guys,

I've tried several different things with the following 2 queries but
nothing seem to help. I've stared at the code and reworked it for hours
on end and frankly I am getting frustrated.

I'm only posting the queries themselves without the PHP code as the
entire section is quite long. If someone wants it I'd be more than happy
to post it as well.

ANY help or guidance is very much appreciated. I'm so far beyond
frustrated it isn't funny :-(

Thanks in advance.

Aaron


CODE:


$mQuery = db_query("SELECT DISTINCT manufacturer FROM kcs_threads ORDER
BY manufacturer");
while ($mResult = db_fetch($mQuery)) {

//HTMl here

$query = "select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl
FROM kcs_threads t LEFT JOIN kcs_threadgroups g ON
t.type = g.threadType
WHERE t.manufacturer =
".escapeQuote($mResult['manufacturer'])."
ORDER BY t.type, g.groupName";

$result = db_query($query);

while($thread = db_fetch($result)) {

//If validations and HTML display here

}
}


------=_NextPart_000_002B_01C35A6D.9945F620--

Limit

I don't want to use the limit on this query anymore. What I want is to
display all that are dated within the last 3 months. What would be the easy
way to do that?


SELECT federal_development_id, UNIX_TIMESTAMP(date) AS date, topic, body
FROM federal_developments
WHERE pro_solutions = 'Y'
ORDER BY date DESC
LIMIT 5";


Thanks,

MAO



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Limit

If it is a unix timestamp, why not just select rows where the date is >= 7776000
(which is 90 days) or if you want to be specific about the months, use mktime()
to generate the timestamp for the date 3 months ago.

--
Adam Alkins
http://www.rasadam.com


Quoting Marie Osypian :

> I don't want to use the limit on this query anymore. What I want is to
> display all that are dated within the last 3 months. What would be the easy
> way to do that?
>
>
> SELECT federal_development_id, UNIX_TIMESTAMP(date) AS date, topic, body
> FROM federal_developments
> WHERE pro_solutions = 'Y'
> ORDER BY date DESC
> LIMIT 5";
>
>
> Thanks,
>
> MAO
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: query is returning over 74,000 results and taking 30 seconds - HELP!

Have you tried the MySQL EXPLAIN command to look at your statement?
That can provide some insight when nothing else works.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: RE: query is returning over 74,000 results and taking 30 seconds - HELP!

------=_NextPart_000_003A_01C35A93.3DE1DAD0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Ok.. I echo'd out the queries AND the num_rows returned for each loop
and this is what I got:


Manufacturer Query is: SELECT DISTINCT manufacturer FROM kcs_threads
ORDER BY manufacturer

Manufacturer Rows Returned Are: 5


Left Join Query is: select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl FROM kcs_threads t LEFT JOIN
kcs_threadgroups g ON t.type = g.threadType WHERE t.manufacturer = 'DMC'
ORDER BY t.type, g.groupName

Left Join Rows Returned Are: 77022


Left Join Query is: select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl FROM kcs_threads t LEFT JOIN
kcs_threadgroups g ON t.type = g.threadType WHERE t.manufacturer =
'Gentle Arts Sampler Threads' ORDER BY t.type, g.groupName

Left Join Rows Returned Are: 94


Left Join Query is: select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl FROM kcs_threads t LEFT JOIN
kcs_threadgroups g ON t.type = g.threadType WHERE t.manufacturer =
'Kreinik' ORDER BY t.type, g.groupName

Left Join Rows Returned Are: 309


Left Join Query is: select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl FROM kcs_threads t LEFT JOIN
kcs_threadgroups g ON t.type = g.threadType WHERE t.manufacturer =
'Rainbow Gallery' ORDER BY t.type, g.groupName

Left Join Rows Returned Are: 23


Left Join Query is: select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl FROM kcs_threads t LEFT JOIN
kcs_threadgroups g ON t.type = g.threadType WHERE t.manufacturer = 'The
Caron Collection' ORDER BY t.type, g.groupName

Left Join Rows Returned Are: 495



Total elapsed time = 21.905001 seconds. Total results: 77943

What do I make of this? I see where the bottle neck is.. but how to I
refine the LEFT JOIN query to prevent this?

For the DMC Left Join.. is should have returned: 2067 rows in set (1.63
sec)

ANY idea's?

Aaron
-----Original Message-----
From: Nelson Goforth [mailto:ngoforth@earthnet.net]
Sent: August 4, 2003 2:16 PM
To: php-db@lists.php.net
Subject: [PHP-DB] RE: query is returning over 74,000 results and taking
30 seconds - HELP!

Have you tried the MySQL EXPLAIN command to look at your statement?
That can provide some insight when nothing else works.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


------=_NextPart_000_003A_01C35A93.3DE1DAD0--

Re: Limit

From: "Marie Osypian"
> I don't want to use the limit on this query anymore. What I want is to
> display all that are dated within the last 3 months. What would be the
easy
> way to do that?
>
>
> SELECT federal_development_id, UNIX_TIMESTAMP(date) AS date, topic, body
> FROM federal_developments
> WHERE pro_solutions = 'Y'
AND date > CURDATE() - INTERVAL 3 MONTH
> ORDER BY date DESC
";

---John Holmes...


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: LIMIT

What about 'select count(*) from aTable'

- Frank

> Is there a way to get the number of rows that would have been returned
had
> there not been a LIMIT clause in a SELECT statement?
>
> For example, if
> Query #1) SELECT * FROM aTable
> would normally return 100 rows. But
> Query #2) SELECT * FROM aTable LIMIT 5
> will return 5 rows. Is there a way to find out that 100 rows would
have
> been returned if there was no LIMIT clause, by using only Query #2 and
maybe
> a PHP function on the $result?
>
> Regards,
> Dwight
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: LIMIT

Thanks, but that's an additional query.

I was wondering if there may be a PHP function that can operate on the
$result or perhaps $link of the single query that uses a LIMIT clause and
have the information [count(*) had there not been a LIMIT clause
"count_no_limit(*) maybe?" even though there was a LIMIT clause]. Something
like mysql_info.

Regards,
Dwight
x2407

> -----Original Message-----
> From: Frank M. Kromann [mailto:frank@kromann.info]
> Sent: Thursday, June 29, 2006 5:46 PM
> To: Dwight Altman
> Cc: php-db@lists.php.net
> Subject: Re: [PHP-DB] LIMIT
>
> What about 'select count(*) from aTable'
>
> - Frank
>
> > Is there a way to get the number of rows that would have been returned
> had
> > there not been a LIMIT clause in a SELECT statement?
> >
> > For example, if
> > Query #1) SELECT * FROM aTable
> > would normally return 100 rows. But
> > Query #2) SELECT * FROM aTable LIMIT 5
> > will return 5 rows. Is there a way to find out that 100 rows would
> have
> > been returned if there was no LIMIT clause, by using only Query #2 and
> maybe
> > a PHP function on the $result?
> >
> > Regards,
> > Dwight
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: LIMIT

On 7/1/06, Dwight Altman wrote:
> Thanks, but that's an additional query.
>
> I was wondering if there may be a PHP function that can operate on the
> $result or perhaps $link of the single query that uses a LIMIT clause and
> have the information [count(*) had there not been a LIMIT clause
> "count_no_limit(*) maybe?" even though there was a LIMIT clause]. Something
> like mysql_info.

Can the database do it like that? If the answer is no then there's no
way php can do it.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: LIMIT

I am not sure that any dbs will do that....i usually run two queries (one
for the limit data and one for the total rows)

Bastien


>From: "chris smith"
>To: "Dwight Altman"
>CC: php-db@lists.php.net
>Subject: Re: [PHP-DB] LIMIT
>Date: Sat, 1 Jul 2006 18:51:11 +1000
>
>On 7/1/06, Dwight Altman wrote:
>>Thanks, but that's an additional query.
>>
>>I was wondering if there may be a PHP function that can operate on the
>>$result or perhaps $link of the single query that uses a LIMIT clause and
>>have the information [count(*) had there not been a LIMIT clause
>>"count_no_limit(*) maybe?" even though there was a LIMIT clause].
>>Something
>>like mysql_info.
>
>Can the database do it like that? If the answer is no then there's no
>way php can do it.
>
>--
>Postgresql & php tutorials
>http://www.designmagick.com/
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: LIMIT

chris smith wrote:
> On 7/1/06, Dwight Altman wrote:
>
>> Thanks, but that's an additional query.
>>
>> I was wondering if there may be a PHP function that can operate on the
>> $result or perhaps $link of the single query that uses a LIMIT clause and

I'm not sure how this would be done in PHP (simply because I am not a
PHP coder) but in Perl (as an example of the principle, so please keep
your flames at bay for th etime being ;p) I'd do this:

my $results_arrayref = $dbh->selectall_arrayref($query);

my $number_of_results = @{ $results_arrayref };

print "Number of results: $number_of_results\n";

for my $record( @{ $results_arrayref } ) {

# do what you want with each record...
}

I'm sure PHP has a way to count how many is in a given result set
without needing an additional query.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: LIMIT

Your example predicates that the resultset it passed to an array which you
then take the size of, which would only give you the LIMIT value if that
clause is specified in the query...

Bastien

>From: "JupiterHost.Net"
>To: php-db@lists.php.net
>Subject: Re: [PHP-DB] LIMIT
>Date: Sat, 01 Jul 2006 10:59:02 -0500
>
>
>
>chris smith wrote:
>>On 7/1/06, Dwight Altman wrote:
>>
>>>Thanks, but that's an additional query.
>>>
>>>I was wondering if there may be a PHP function that can operate on the
>>>$result or perhaps $link of the single query that uses a LIMIT clause and
>
>I'm not sure how this would be done in PHP (simply because I am not a PHP
>coder) but in Perl (as an example of the principle, so please keep your
>flames at bay for th etime being ;p) I'd do this:
>
> my $results_arrayref = $dbh->selectall_arrayref($query);
>
> my $number_of_results = @{ $results_arrayref };
>
> print "Number of results: $number_of_results\n";
>
> for my $record( @{ $results_arrayref } ) {
>
> # do what you want with each record...
> }
>
>I'm sure PHP has a way to count how many is in a given result set without
>needing an additional query.
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: LIMIT

Bastien Koert wrote:
>
> Your example predicates that the resultset it passed to an array which
> you then take the size of, which would only give you the LIMIT value if
> that clause is specified in the query...

I see, I misunderstood the OPs question then they wanted to know that
there are 25000 records that match their WHERE but only SELECT 100 of them.

Gotcha ;p

yeah thats pretty much got to be 2 queries, although if caching is
involved it might not be that expensive...

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: LIMIT

I see they call mysql_query twice which doesn't look like I'm gaining
anything over running the 2 queries:
SELECT count(*) as MyCount FROM aTable
SELECT * FROM aTable LIMIT 5

But I suppose I need to time it for my app.

http://us3.php.net/mysql_num_rows

mancini at nextcode dot org
14-Nov-2005 02:24
here is a really fast mysql_num_rows alternative that makes use of the
SELECT FOUND_ROWS() MySQL function , it only reads a single row and it is
really helpfull if you are counting multiple tables with thousands of rows

function get_rows ($table) {
$temp = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM $table LIMIT
1");
$result = mysql_query("SELECT FOUND_ROWS()");
$total = mysql_fetch_row($result);
return $total[0];
}
?>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++
alex dot feinberg 4t gm41l
28-Apr-2005 04:56
Re dzver at abv dot bg's note...

I just ran some tests using MySQL Super Smack. Surprisingly, a SELECT *
followed by a SELECT COUNT(*) actually was close in speed to a SELECT
SQL_CALC_FOUND_ROWS * followed by a SELECT FOUND_ROWS(), but the
SQL_CALC_FOUND_ROWS solution was still a bit faster.

Perhaps it varies by table structure? Either way, it might be worth checking
which is faster for your application.

Regards,
Dwight

> -----Original Message-----
> From: Dwight Altman [mailto:dwight@multicam.com]
> Sent: Wednesday, July 05, 2006 9:53 AM
> To: 'php-db@lists.php.net'
> Subject: RE: [PHP-DB] LIMIT
>
> So how do I get this information on the PHP side?
>
> mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
> -> WHERE id > 100 LIMIT 10;
> mysql> SELECT FOUND_ROWS();
>
> I count 2 queries above (unless there is some caching magic). Whatever.
> I was hoping to add another column (like FOUND_ROWS) to an existing query
> and be able to pull out the FOUND_ROWS when I loop over the result set.
> Something like:
> SELECT *, FOUND_ROWS FROM aTable LIMIT 5
>
> But since that single query doesn't work, how do I apply the MySQL
> solution and extract it on the PHP side?
>
> http://dev.mysql.com/doc/refman/4.1/en/information-functions .html
> http://www.mysqlfreaks.com/statements/101.php
>
>
> Regards,
> Dwight
>
> > -----Original Message-----
> > From: Chris [mailto:listschris@leftbrained.org]
> > Sent: Friday, June 30, 2006 9:46 AM
> > To: php-db@lists.php.net
> > Subject: Re: [PHP-DB] LIMIT
> >
> > If you're using MySQL then:
> >
> > SELECT SQL_CALC_FOUND_ROWS * FROM aTable LIMIT 5
> > SELECT FOUND_ROWS()
> >
> > It's in the mysql documentation under SELECT syntax I believe.
> >
> > Chris
> >
> > Dwight Altman wrote:
> > > Is there a way to get the number of rows that would have been returned
> > had
> > > there not been a LIMIT clause in a SELECT statement?
> > >
> > > For example, if
> > > Query #1) SELECT * FROM aTable
> > > would normally return 100 rows. But
> > > Query #2) SELECT * FROM aTable LIMIT 5
> > > will return 5 rows. Is there a way to find out that 100 rows would
> have
> > > been returned if there was no LIMIT clause, by using only Query #2 and
> > maybe
> > > a PHP function on the $result?
> > >
> > > Regards,
> > > Dwight
> > >
> > >
> > >
> >
> > --

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: LIMIT

Here are some thoughts on the couple of methods shown:

1.
SELECT count(*) as MyCount FROM aTable # Fast query done on server size
SELECT * FROM aTable LIMIT 5 # Fast limited data return query done server side

2.
SELECT * FROM aTable # Potentially slow query, lots of data stored in PHP
mysql_num_rows() # Quick PHP-side function to tell how many results returned

3.
SELECT SQL_CALC_FOUND_ROWS * FROM $table LIMIT 5 # Fast server side query that does the same as "SELECT count(*)" and "SELECT LIMIT 5" at the same time
SELECT FOUND_ROWS() # Fast server side query


Seems like #1 and #3 are your best options. Both are pretty easy on your system. #3 is definitely pretty slick, thanks to whoever originally posted that. But I'm wondering if it'll be fully compatible in the future. #1 is pretty "SQL 101" type stuff, so it should remain functional and be usable on other database platforms as well. That's the one I'd go for, just for the sake of keeping things simple if nothing else.

Seems like a waste to do two database queries, but as long as you're not closing and re-opening the connection in between, it should cause very minimal impact on your system. Sometimes two queries are better than one.

-TG


= = = Original message = = =

I see they call mysql_query twice which doesn't look like I'm gaining
anything over running the 2 queries:
SELECT count(*) as MyCount FROM aTable
SELECT * FROM aTable LIMIT 5

But I suppose I need to time it for my app.

http://us3.php.net/mysql_num_rows

mancini at nextcode dot org
14-Nov-2005 02:24
here is a really fast mysql_num_rows alternative that makes use of the
SELECT FOUND_ROWS() MySQL function , it only reads a single row and it is
really helpfull if you are counting multiple tables with thousands of rows

function get_rows ($table)
$temp = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM $table LIMIT
1");
$result = mysql_query("SELECT FOUND_ROWS()");
$total = mysql_fetch_row($result);
return $total[0];

?>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++
alex dot feinberg 4t gm41l
28-Apr-2005 04:56
Re dzver at abv dot bg's note...

I just ran some tests using MySQL Super Smack. Surprisingly, a SELECT *
followed by a SELECT COUNT(*) actually was close in speed to a SELECT
SQL_CALC_FOUND_ROWS * followed by a SELECT FOUND_ROWS(), but the
SQL_CALC_FOUND_ROWS solution was still a bit faster.

Perhaps it varies by table structure? Either way, it might be worth checking
which is faster for your application.

Regards,
Dwight

> -----Original Message-----
> From: Dwight Altman [mailto:dwight@multicam.com]
> Sent: Wednesday, July 05, 2006 9:53 AM
> To: 'php-db@lists.php.net'
> Subject: RE: [PHP-DB] LIMIT
>
> So how do I get this information on the PHP side?
>
> mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
> -> WHERE id > 100 LIMIT 10;
> mysql> SELECT FOUND_ROWS();
>
> I count 2 queries above (unless there is some caching magic). Whatever.
> I was hoping to add another column (like FOUND_ROWS) to an existing query
> and be able to pull out the FOUND_ROWS when I loop over the result set.
> Something like:
> SELECT *, FOUND_ROWS FROM aTable LIMIT 5
>
> But since that single query doesn't work, how do I apply the MySQL
> solution and extract it on the PHP side?
>
> http://dev.mysql.com/doc/refman/4.1/en/information-functions .html
> http://www.mysqlfreaks.com/statements/101.php
>
>
> Regards,
> Dwight
>
> > -----Original Message-----
> > From: Chris [mailto:listschris@leftbrained.org]
> > Sent: Friday, June 30, 2006 9:46 AM
> > To: php-db@lists.php.net
> > Subject: Re: [PHP-DB] LIMIT
> >
> > If you're using MySQL then:
> >
> > SELECT SQL_CALC_FOUND_ROWS * FROM aTable LIMIT 5
> > SELECT FOUND_ROWS()
> >
> > It's in the mysql documentation under SELECT syntax I believe.
> >
> > Chris
> >
> > Dwight Altman wrote:
> > > Is there a way to get the number of rows that would have been returned
> > had
> > > there not been a LIMIT clause in a SELECT statement?
> > >
> > > For example, if
> > > Query #1) SELECT * FROM aTable
> > > would normally return 100 rows. But
> > > Query #2) SELECT * FROM aTable LIMIT 5
> > > will return 5 rows. Is there a way to find out that 100 rows would
> have
> > > been returned if there was no LIMIT clause, by using only Query #2 and
> > maybe
> > > a PHP function on the $result?
> > >
> > > Regards,
> > > Dwight
> > >
> > >
> > >
> >
> > --

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php