I agree with Jasper here, you want to make it a point to optimize
your queries so that fewer trips are made to the database server. On
the topic of indexing, I'm curious as to how you did that and if your
using it as intended. In your query you are testing columns
`time`,`date` so you'll want to make sure that those are two columns
that have been indexed.
You might want to investigate different types of indexes and how to
optimize them too. In my own experience I know that FULLTEXT indexes
work wonders for large 'search engine' tables.
Multiple Column index
- http://dev.mysql.com/doc/mysql/en/multiple-column-indexes.html
FULLTEXT index
- http://dev.mysql.com/doc/mysql/en/fulltext-search.html
Another thing to look at is optimizing your column types. If for
instance you have single characters stored in a column that is
defined as type BLOB instead of CHAR, your going to slow your queries
a bit.
- http://dev.mysql.com/doc/mysql/en/data-size.html
Good luck!
-Joe
On Jul 23, 2005, at 6:18 AM, Jasper Bryant-Greene wrote:
balwant singh wrote:
Hi,
I have made a webpage in php which retrieves the data from a huge
MYSQL tables as below:-
$i=0;
while ($i<=23)
{
select a, b from table where hour(time)=$i and date='' order by
desc time limit 1;
$i++;
}
then i substract the current hour output from the last hour output
for calcuating the output in that hour through PHP code and then
display it.
But my problem is that this page takes approx. 90 seconds or more
for loading even when i refresh the page, it is taking the same time.
I have done indexing on that table, tune the php and apache but it
is still taking same time.
I have other tables also when PHP is doing more calculatios but
those pages are not taking so much time they load in 5 sec. or so.
Is there anything needs to be done more, please advise.
Don't loop through in PHP and execute the query 23 times. Instead,
execute the query once (without the "hour(time)=$1 and " and the
"limit 1", and use a while($row = mysql_fetch_assoc($result)) { }
type function to get all 23 rows. That will be much faster.
By the way, that should probably be "order by time desc" not "order
by desc time".
Jasper
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
