Results in multiple pages. Takes too much time

Hi,
I have a table of a million records and wrote a CGI-PERL script to
display the results based on the user input. The results might be
anywhere from 100 to 1000 per query and presently I am displaying them
as 25 results per page.

Problem: Each query is taking about 20-30 seconds.

My solution: I have tried to optimize the table and also index the
table. I have actually converted a MS access database to SQL database,
so it wasn't previously indexed. Both optimization and indexing doesn't
give any good results. I always get a timeout. ie. it takes longer
after indexing and optimizing.

1. I was wondering if someone has a creative solution for this. ie.
reduce the time from 20-30 seconds to atleast 10 seconds.

2. I have links of pages of results beneath the first page result. When
each of these links are clicked it takes 20-30 seconds again. Is there
a way I can reduce the time taken for the subsequent pages are reduced?
I cannot use the LIMIT option in mysql, since I have a where clause
which has to search through the whole table. I tried using views and
using limits, but it takes as much time.

Please let me know.

Thanks.

Re: Results in multiple pages. Takes too much time

premgrps@gmail.com wrote:
> Hi,
> I have a table of a million records and wrote a CGI-PERL script to
> display the results based on the user input. The results might be
> anywhere from 100 to 1000 per query and presently I am displaying them
> as 25 results per page.
>
> Problem: Each query is taking about 20-30 seconds.
>
> My solution: I have tried to optimize the table and also index the
> table. I have actually converted a MS access database to SQL database,
> so it wasn't previously indexed. Both optimization and indexing doesn't
> give any good results. I always get a timeout. ie. it takes longer
> after indexing and optimizing.
>
> 1. I was wondering if someone has a creative solution for this. ie.
> reduce the time from 20-30 seconds to atleast 10 seconds.
>
> 2. I have links of pages of results beneath the first page result. When
> each of these links are clicked it takes 20-30 seconds again. Is there
> a way I can reduce the time taken for the subsequent pages are reduced?
> I cannot use the LIMIT option in mysql, since I have a where clause
> which has to search through the whole table. I tried using views and
> using limits, but it takes as much time.
>

At first glance, this is more of a MySQL question than a Perl question.
How long does a typical query take if executed at the MySQL prompt? What
happens when you prefix the query with EXPLAIN? You may find you don't
have all the indexes you need.

If the query doesn't run fast by itself, no amount of tuning in Perl is
going to help.

Mark

Re: Results in multiple pages. Takes too much time

premgrps@gmail.com wrote:
> I have a table of a million records and wrote a CGI-PERL script to
> display the results based on the user input. The results might be
> anywhere from 100 to 1000 per query and presently I am displaying
> them as 25 results per page.
>
> Problem: Each query is taking about 20-30 seconds.
>
> My solution: I have tried to optimize the table and also index the
> table. I have actually converted a MS access database to SQL
> database, so it wasn't previously indexed. Both optimization and
> indexing doesn't give any good results. I always get a timeout. ie.
> it takes longer after indexing and optimizing.
>
> 1. I was wondering if someone has a creative solution for this. ie.
> reduce the time from 20-30 seconds to atleast 10 seconds.
>
> 2. I have links of pages of results beneath the first page result.
> When each of these links are clicked it takes 20-30 seconds again.
> Is there a way I can reduce the time taken for the subsequent pages
> are reduced? I cannot use the LIMIT option in mysql, since I have
> a where clause which has to search through the whole table. I tried
> using views and using limits, but it takes as much time.

This is all SQL-related information. X-posting an SQL problem to
a computer language group doesn't help anyone.

Other information that might be helpful might include: number of
records in the table being queried; number of tables/joins/etc used to
generate queries; available memory on both the database server *and*
the web server.

And, the most important thing to post to a programming newsgroup:
as much actual and relevant code as necessary to produce the results
discussed.

It might be possible the CGI script is causing a significant portion
of your delay. If so, we need Perl code before we can help. If not,
then this is off-topic and doesn't belong here.

Cheers,
Tim Hammerquist

Re: Results in multiple pages. Takes too much time

premgrps@gmail.com wrote:

> Hi,
> I have a table of a million records and wrote a CGI-PERL script to
> display the results based on the user input.

Post some code, it seems to me that you may be doing something wrong,
wrong, wrong... I'm assuming you are using DBI, just post the execute
and fetch part of the code.


The results might be
> anywhere from 100 to 1000 per query and presently I am displaying them
> as 25 results per page.
>
> Problem: Each query is taking about 20-30 seconds.

Could be for non indexed... What do the indexes look like and what
does your SQL look like?
>
> My solution: I have tried to optimize the table and also index the
> table. I have actually converted a MS access database to SQL database,
> so it wasn't previously indexed. Both optimization and indexing doesn't
> give any good results. I always get a timeout. ie. it takes longer
> after indexing and optimizing.
>
> 1. I was wondering if someone has a creative solution for this. ie.
> reduce the time from 20-30 seconds to atleast 10 seconds.
>
> 2. I have links of pages of results beneath the first page result. When
> each of these links are clicked it takes 20-30 seconds again. Is there
> a way I can reduce the time taken for the subsequent pages are reduced?
> I cannot use the LIMIT option in mysql,

Sure you can, limit has a "start" and an "end" value. Frankly I don't
know how they do this in SQL Server where you only have top.

Jeff


since I have a where clause
> which has to search through the whole table. I tried using views and
> using limits, but it takes as much time.
>
> Please let me know.
>
> Thanks.
>

Re: Results in multiple pages. Takes too much time

premgrps@gmail.com wrote:

> Hi,
> I have a table of a million records and wrote a CGI-PERL script to
> display the results based on the user input.

Post some code, it seems to me that you may be doing something wrong,
wrong, wrong... I'm assuming you are using DBI, just post the execute
and fetch part of the code.


The results might be
> anywhere from 100 to 1000 per query and presently I am displaying them
> as 25 results per page.
>
> Problem: Each query is taking about 20-30 seconds.

Could be for non indexed... What do the indexes look like and what
does your SQL look like?
>
> My solution: I have tried to optimize the table and also index the
> table. I have actually converted a MS access database to SQL database,
> so it wasn't previously indexed. Both optimization and indexing doesn't
> give any good results. I always get a timeout. ie. it takes longer
> after indexing and optimizing.
>
> 1. I was wondering if someone has a creative solution for this. ie.
> reduce the time from 20-30 seconds to atleast 10 seconds.
>
> 2. I have links of pages of results beneath the first page result. When
> each of these links are clicked it takes 20-30 seconds again. Is there
> a way I can reduce the time taken for the subsequent pages are reduced?
> I cannot use the LIMIT option in mysql,

Sure you can, limit has a "start" and an "end" value. Frankly I don't
know how they do this in SQL Server where you only have top.

Jeff


since I have a where clause
> which has to search through the whole table. I tried using views and
> using limits, but it takes as much time.
>
> Please let me know.
>
> Thanks.
>