Re: Speeding up MySQL query

EmmettPower@xxxxxxxxx wrote:

> Hi,
>
> I am reusing the results of a MySQL query on a PHP page. The code works
> fine but it is taking about 4 to 5 seconds to load the page so I am
> wondering if there is a way to make the code more efficient and the
> page load more quickly.
>
> At the top of the page I am running the query with the following code.
> $id loads through a session variable:
>
> <?
>
> ini_set('max_execution_time', 86400);
> $link = mysql_connect($host, $uid, $pwd);
> mysql_select_db('Database');
> $query = "SELECT DISTINCT `Table`.`Field` FROM `Table` WHERE
> `Table`.`ID` = $id ORDER BY Table`.`Field`";
> $result = mysql_query($query) or die (mysql_error());
>
> ?>
>
> When I want to access the query data I run the following code.
>
> <?php
>
> $i=0;
> mysql_data_seek($result,0);
> while ($array= mysql_fetch_array($result)) {
> ?>
>
> HTML
> <?php echo $array[Field] ?>">
> HTML
>
> <?php
> $i++;
> }
> ?>
>
> This code is repeated about four times on the page.
>
> I'd appreciate any suggestions as to how to speed this up.
>
> Regards
>
> Emmett


Hi Emmett,

It looks pretty optimized to me...
Allthough DISTINCT can be a bottleneck in some situations.

To find ways to optimize, first find the bottlenecks.
To do this, make a rude benchmark, using systemtime in milliseconds.

Just measure the time needed to:
1) execute the query.
2) Loop over the results.

Maybe the query is extremely fast, and the bottleneck is somewhere else in
your code.


Two other ideas:
1) If you are producing a HUGE page: consider using compression (lookup zlib
on www.php.net)
2) If you are running over the same query 4 times, it might help to remember
the results maybe in a variable. Not sure, it depends.
Again: measure it in milliseconds, then you know something. Now is is
guessing. :-)

Good luck,
Regards,
Erwin Moller
.

Relevant Pages

  • Re: To load or not to load
    ... First, write a query like ... >it should be 1 and therefore load. ... what you are looking for, chr, is not in this string... ... shouldn't the DCount solution work - you are right, ...
    (microsoft.public.access.formscoding)
  • User Input in OnLoad Form Event
    ... The second form holds data fed from a query, ... The second form loads from the query. ... The list boxes in the second form requery on load. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Access General Inquiry
    ... Form_BeforeUpdate for record validation, ... a table or query as datasource. ... Or the form would load with empty fields, ... datatable or query as a recordsource with wizard functionality built into ...
    (microsoft.public.access.forms)
  • Re: FIELD CALCULATION
    ... Create a query based on tblLoadLeg. ... Create a main form bound to tblLoad. ... There's probably much more than this to it, and there are better ways of handling the mileage for each leg (so the database automatically uses the last HubEnd and LocEnd without having to enter them again), but that should get you started. ... If you have one load number that represents the entire distance, ...
    (microsoft.public.access.forms)
  • Re: query speed
    ... If there aren't any rows in tbl_archive, just load the data w/o the LEFT ... duplicates (like your query), the 1st query always runs slowly (or not ... Ideally, I want this query to prevent records from being appended to tbl_archive if the record is already there for a loan acct # for that date and with that status is already there, but it is even slower, and I am not even sure if the syntax is even right to achieve this, as I got multiple same-records appended: ...
    (microsoft.public.access.queries)