Professional Web Applications Themes

Results in multiple pages. Takes too much time - MySQL

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 ...

  1. #1

    Default 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.

    premgrps@gmail.com Guest

  2. #2

    Default Re: Results in multiple pages. Takes too much time

    [email]premgrps[/email] 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
    Mark Clements Guest

  3. #3

    Default Re: Results in multiple pages. Takes too much time

    [email]premgrps[/email] 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.
    >
    Jeff Guest

Similar Threads

  1. dsa.msc-> Sending To Home Folders Takes Longer Time!!
    By Hareth in forum Windows Server
    Replies: 1
    Last Post: July 6th, 03:00 AM
  2. Illustrator takes a long time to become active when switching between programs
    By jonathan_himmel@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 7
    Last Post: April 23rd, 10:49 PM
  3. it takes time to load
    By FJames webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 0
    Last Post: January 17th, 07:46 AM
  4. Movie takes time to start loading
    By Maximum3D webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 0
    Last Post: October 28th, 07:20 PM
  5. View Workgroup Computers takes a long time
    By Joe Dolsak in forum Windows XP/2000/ME
    Replies: 1
    Last Post: July 3rd, 07:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139