Can this Query be made faster?

..
The UK charity I periodically give IT support to has a database with a
client table which currently contains some 600 records but will soon
contain over 10,000.
Currently there are 31 columns in the table; this may increase to 35;
none will contain more than some 2000 characters, few more than 300.
All the appropriate indices have been set up.
Due to sloppy (paper-borne) record-keeping for the last ten years
before computers were introduced, part of the use of the database is
displaying all the data items to allow checking for duplicates and
typos.
For the next few years (until present clients begin to pass away and
new clients are qualified better when adding them) there is no real
alternative to extracting all the records each Query.

The extract Query I'm using is the simple -

$TableName="clients";
$Query="SELECT * FROM $TableName order by blah-blah";
$Result=mysql_db_query ($DBName, $Query, $Link);
while ($Row=mysql_fetch_array ($Result))
{
display each client's data in one line across the screen
}

I'm concerned that this may seem slow when handling the enlarged
number of records. Is there any way to speed this up?

Is MySQL up to the task, or do I need to investigate another RDMS?

If we bought/rented a dedicated server, would that help more than a
new RDMS?

TIA.

Re: Can this Query be made faster?

haggisbasher@nerdshack.com wrote:

> For the next few years (until present clients begin to pass away and
> new clients are qualified better when adding them) there is no real
> alternative to extracting all the records each Query.
>
> The extract Query I'm using is the simple -
>
> $TableName="clients";
> $Query="SELECT * FROM $TableName order by blah-blah";
> $Result=mysql_db_query ($DBName, $Query, $Link);
> while ($Row=mysql_fetch_array ($Result))
> {
> display each client's data in one line across the screen
> }
>
> I'm concerned that this may seem slow when handling the enlarged
> number of records. Is there any way to speed this up?

As you are displaying so much about each row and I hardly think you will
generate a page which has all the 10000 entries shown at the same time, using
LIMIT will speed it up a bit.

http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.ht ml


> Is MySQL up to the task, or do I need to investigate another RDMS?

A couple of years ago they made a big test on SQL servers, where each SQL
server developer was allowed to send a team to configure the server for best
result. At this point the MySQL4 wasn't more than at Alpha stage, but MySQL
choose to use it and took second place after Oracle, as Oracle had more features.
Google.com uses MySQL, so if they are happy with it, I guess your 10000 rows
of data won't be a match for MySQL.


> If we bought/rented a dedicated server, would that help more than a
> new RDMS?

Of course a dedicated server would increase the performance of the SQL server,
no matter if you use MySQL or the damn slow MSSQL, but remember that if you
have to run the queries over TCP, you will find slowdowns, specially if the
web server and sql server are located far away (long route for the packages to
be transported).


--

//Aho

Re: Can this Query be made faster?

On Mon, 25 Dec 2006 13:26:10 +0100, "J.O. Aho"
wrote:

>haggisbasher@nerdshack.com wrote:
>
>> For the next few years (until present clients begin to pass away and
>> new clients are qualified better when adding them) there is no real
>> alternative to extracting all the records each Query.
>>
>> The extract Query I'm using is the simple -
>>
>> $TableName="clients";
>> $Query="SELECT * FROM $TableName order by blah-blah";
>> $Result=mysql_db_query ($DBName, $Query, $Link);
>> while ($Row=mysql_fetch_array ($Result))
>> {
>> display each client's data in one line across the screen
>> }
>>
>> I'm concerned that this may seem slow when handling the enlarged
>> number of records. Is there any way to speed this up?
>
>As you are displaying so much about each row and I hardly think you will
>generate a page which has all the 10000 entries shown at the same time, using
>LIMIT will speed it up a bit.
>
>http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.h tml
>
>
>> Is MySQL up to the task, or do I need to investigate another RDMS?
>
>A couple of years ago they made a big test on SQL servers, where each SQL
>server developer was allowed to send a team to configure the server for best
>result. At this point the MySQL4 wasn't more than at Alpha stage, but MySQL
>choose to use it and took second place after Oracle, as Oracle had more features.
>Google.com uses MySQL, so if they are happy with it, I guess your 10000 rows
>of data won't be a match for MySQL.
>
>
>> If we bought/rented a dedicated server, would that help more than a
>> new RDMS?
>
>Of course a dedicated server would increase the performance of the SQL server,
>no matter if you use MySQL or the damn slow MSSQL, but remember that if you
>have to run the queries over TCP, you will find slowdowns, specially if the
>web server and sql server are located far away (long route for the packages to
>be transported).

Thank you, your answer seems quite reassuring. I foresee my customer
query screen having to offer alphabetic options and my query using
BETWEEN - although I suppose that, at some time, I was going to have
to go that route anyway..

Re: Can this Query be made faster?

As stated in the above answer you probably should use LIMIT and divide the
results into pages sorting the records as you please.

A small search form probably would solve a lot too.


schreef in bericht
news:2cevo2lhd35oalu2tl4mov8c7207uh52b1@4ax.com...
> .
> The UK charity I periodically give IT support to has a database with a
> client table which currently contains some 600 records but will soon
> contain over 10,000.
> Currently there are 31 columns in the table; this may increase to 35;
> none will contain more than some 2000 characters, few more than 300.
> All the appropriate indices have been set up.
> Due to sloppy (paper-borne) record-keeping for the last ten years
> before computers were introduced, part of the use of the database is
> displaying all the data items to allow checking for duplicates and
> typos.
> For the next few years (until present clients begin to pass away and
> new clients are qualified better when adding them) there is no real
> alternative to extracting all the records each Query.
>
> The extract Query I'm using is the simple -
>
> $TableName="clients";
> $Query="SELECT * FROM $TableName order by blah-blah";
> $Result=mysql_db_query ($DBName, $Query, $Link);
> while ($Row=mysql_fetch_array ($Result))
> {
> display each client's data in one line across the screen
> }
>
> I'm concerned that this may seem slow when handling the enlarged
> number of records. Is there any way to speed this up?
>
> Is MySQL up to the task, or do I need to investigate another RDMS?
>
> If we bought/rented a dedicated server, would that help more than a
> new RDMS?
>
> TIA.

Re: Can this Query be made faster?

On Mon, 25 Dec 2006 17:55:16 +0100, "Stef" wrote:

>As stated in the above answer you probably should use LIMIT and divide the
>results into pages sorting the records as you please.
>
>A small search form probably would solve a lot too.

He stated that main use was to search for duplicates/typos, so might
also be a nice feature for the users if you make the column headers
clickable with hrefs.
Clicks will re-query using that column as the ORDER BY column.
pretty easy to do and I've found that Admin users quite like the
feature for looking at "arw" data.
--
Locate your Mobile phone:
Great gifts:

Re: Can this Query be made faster?

On Wed, 27 Dec 2006 07:42:41 +0000, David Quinton
wrote:

>On Mon, 25 Dec 2006 17:55:16 +0100, "Stef" wrote:
>
>>As stated in the above answer you probably should use LIMIT and divide the
>>results into pages sorting the records as you please.
>>
>>A small search form probably would solve a lot too.
>
>He stated that main use was to search for duplicates/typos, so might
>also be a nice feature for the users if you make the column headers
>clickable with hrefs.
>Clicks will re-query using that column as the ORDER BY column.
>pretty easy to do and I've found that Admin users quite like the
>feature for looking at "arw" data.

Thanx for your contribution. That is actually a feature of the
present display (clicking on column headings sorts by that column,
name, postcode, service_number, NI_number, etc) so I'll be continuing
that but with the same mysql BETWEEN instruction as the original
Query.