Code optimization

Is there a way to optimize this with better mySQL query?

# Select today's Bible verse

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM verse_of_the_day_Bible_verses WHERE `assigned_date` =
'0000-00-00' AND seasonal_use = $verse_application ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

# Checking to see if each verse has been used once and
# if so reset the used column and select a fresh Bible verse

if ( $num == 0 ) {

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="UPDATE `verse_of_the_day_Bible_verses` SET `assigned_date` =
'0000-00-00' WHERE `assigned_date` <> '0000-00-00' AND `seasonal_use` =
$verse_application";
$result=mysql_query($query);
mysql_close();

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM verse_of_the_day_Bible_verses WHERE `assigned_date` =
'0000-00-00' AND seasonal_use = $verse_application ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

}

$bible_verse_ref=mysql_result($result,0,"bible_verse_ref");
$bible_verse_text=mysql_result($result,0,"bible_verse_text") ;
$bible_verse_translation=mysql_result($result,0,"bible_verse _translation");
$bible_record=mysql_result($result,0,"record");
$bible_store_catalog_reference=mysql_result($result,0,"store _catalog_reference");
$bible_teaching_devotional_messages_reference=mysql_result($ result,0,"store_teaching_devotional_messages_reference");

# Now make the web page show today's Bible verse
# and mark this Bible verse used so it won't be randomly selected from the
database until each verse has been used

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="UPDATE `verse_of_the_day_Bible_verses` SET `assigned_date` =
'$todays_date' WHERE `record` LIKE '$bible_record'";
$result=mysql_query($query);
mysql_close();


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

Re: Code optimization

Ron Piggott wrote:
> Is there a way to optimize this with better mySQL query?

Step 1 - work out which bit is slow.

$start_time = time();
mysql_query (....)

echo "That took " . (time() - $start_time) . " seconds
\n";


I'd guess the first one is slow because of the order by random()

but that's just a guess.

--
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: Code optimization

you can avoid using mysql_connect and mysql_close for every query
(they are the most costliest functions in your application)

you should not use mysql_result for this application
http://in2.php.net/function.mysql-result
try using mysql_fetch_assoc instead.

finally, use a good profiler, like xdebug to find the bottlenecks in
your application.
Kranthi.



On Fri, Aug 7, 2009 at 06:00, Chris wrote:
> Ron Piggott wrote:
>>
>> Is there a way to optimize this with better mySQL query?
>
> Step 1 - work out which bit is slow.
>
> $start_time = time();
> mysql_query (....)
>
> echo "That took " . (time() - $start_time) . " seconds
\n";
>
>
> I'd guess the first one is slow because of the order by random()
>
> but that's just a guess.
>
> --
> 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: Code optimization

kranthi wrote:
> you can avoid using mysql_connect and mysql_close for every query
> (they are the most costliest functions in your application)

You're assuming that. It could be the queries being run are the costliest.

> you should not use mysql_result for this application
> http://in2.php.net/function.mysql-result
> try using mysql_fetch_assoc instead.

It works, it gives the right results - it's perfectly fine.

> finally, use a good profiler, like xdebug to find the bottlenecks in
> your application.

That is a must, xdebug is very very useful.

--
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: Code optimization

>> You're assuming that. It could be the queries being run are the costliest.

It has got nothing to do with the cost of query. bare mysql_connect()
is very costly.

compare
$time = microtime(true);
mysql_connect('localhost', 'user', 'pass');
//mysql_close();
echo (microtime(true) - $time) . "
\n";
$time = microtime(true);
mysql_connect('localhost', 'user', 'pass');
mysql_close();
echo (microtime(true) - $time);

with

$time = microtime(true);
mysql_connect('localhost', 'user', 'pass');
mysql_close();
echo (microtime(true) - $time) . "
\n";
$time = microtime(true);
mysql_connect('localhost', 'user', 'pass');
mysql_close();
echo (microtime(true) - $time);

to see the difference.

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

Re: Code optimization

On Fri, Aug 7, 2009 at 5:10 PM, kranthi wrote:
>>> You're assuming that. It could be the queries being run are the costliest.
>
> It has got nothing to do with the cost of query. bare mysql_connect()
> is very costly.

I'm not arguing with that, I'm saying that the cost of the
mysql_connect could be nothing compared to doing a query on 50,000
rows and doing an order by random(). Of course I'm guessing about the
number of rows - since the OP has given no indication on any of this
it's all speculation until he spends some time figuring this stuff
out.

--
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: Code optimization

On Aug 6, 2009, at 7:52 PM, "Ron Piggott"
wrote:

> Is there a way to optimize this with better mySQL query?
>
> # Select today's Bible verse
>
> mysql_connect('localhost',$username,$password);
> @mysql_select_db($database) or die( "Unable to select database");
> $query="SELECT * FROM verse_of_the_day_Bible_verses WHERE
> `assigned_date` = '0000-00-00' AND seasonal_use = $verse_application
> ORDER BY RAND() LIMIT 1";
> $result=mysql_query($query);
> $num=mysql_numrows($result);
> mysql_close();
>
> # Checking to see if each verse has been used once and
> # if so reset the used column and select a fresh Bible verse
>
> if ( $num == 0 ) {
>
> mysql_connect('localhost',$username,$password);
> @mysql_select_db($database) or die( "Unable to select database");
> $query="UPDATE `verse_of_the_day_Bible_verses` SET `assigned_date` =
> '0000-00-00' WHERE `assigned_date` <> '0000-00-00' AND
> `seasonal_use` = $verse_application";
> $result=mysql_query($query);
> mysql_close();
>
> mysql_connect('localhost',$username,$password);
> @mysql_select_db($database) or die( "Unable to select database");
> $query="SELECT * FROM verse_of_the_day_Bible_verses WHERE
> `assigned_date` = '0000-00-00' AND seasonal_use = $verse_application
> ORDER BY RAND() LIMIT 1";
> $result=mysql_query($query);
> $num=mysql_numrows($result);
> mysql_close();
>
> }
>
> $bible_verse_ref=mysql_result($result,0,"bible_verse_ref");
> $bible_verse_text=mysql_result($result,0,"bible_verse_text") ;
> $bible_verse_translation=mysql_result($result,
> 0,"bible_verse_translation");
> $bible_record=mysql_result($result,0,"record");
> $bible_store_catalog_reference=mysql_result($result,
> 0,"store_catalog_reference");
> $bible_teaching_devotional_messages_reference=mysql_result($ result,
> 0,"store_teaching_devotional_messages_reference");
>
> # Now make the web page show today's Bible verse
> # and mark this Bible verse used so it won't be randomly selected
> from the database until each verse has been used
>
> mysql_connect('localhost',$username,$password);
> @mysql_select_db($database) or die( "Unable to select database");
> $query="UPDATE `verse_of_the_day_Bible_verses` SET `assigned_date` =
> '$todays_date' WHERE `record` LIKE '$bible_record'";
> $result=mysql_query($query);
> mysql_close();
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


Doing the update based in a primary key for the table would be faster
that the various bits of the where clause

Bastien

Sent from my iPod

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