Re: Optimizing a big query...

At 09:18 AM 3/11/2005, sam wun wrote: mos wrote: At 07:01 PM 3/10/2005, Chris W. Parker wrote: Homam S.A. on Wednesday, March 09, 2005 5:09 PM said: > If your tables are mostly read-only, you could > pre-generate page numbers on a periodic basis and > select only spec

Re: Optimizing a big query...

mos wrote: At 07:01 PM 3/10/2005, Chris W. Parker wrote: Homam S.A. on Wednesday, March 09, 2005 5:09 PM said: > If your tables are mostly read-only, you could > pre-generate page numbers on a periodic basis and > select only specific ranges WHERE row_number BETWEEN >

RE: Optimizing a big query...

At 07:01 PM 3/10/2005, Chris W. Parker wrote: Homam S.A. on Wednesday, March 09, 2005 5:09 PM said: > If your tables are mostly read-only, you could > pre-generate page numbers on a periodic basis and > select only specific ranges WHERE row_number BETWEEN > page_start

RE: Optimizing a big query...

Homam S.A. on Wednesday, March 09, 2005 5:09 PM said: > If your tables are mostly read-only, you could > pre-generate page numbers on a periodic basis and > select only specific ranges WHERE row_number BETWEEN > page_start AND page_finish. How about just getting the

Re: Optimizing a big query...

--- mos <[EMAIL PROTECTED]> wrote: > Correct, but the speed difference will more than > make up for it. If you > have a busy website, it is very important not to > overtax the database > server with frills, like the number of rows found. > If you want to have the > record counts, then you will h

Re: Optimizing a big query...

At 07:19 PM 3/9/2005, you wrote: Unfortunately this doesn't work well if you want to tell your users how many pages were found in the query. Correct, but the speed difference will more than make up for it. If you have a busy website, it is very important not to overtax the database server with fr

Re: Optimizing a big query...

Unfortunately this doesn't work well if you want to tell your users how many pages were found in the query. Sure, you could use SQL_CALC_FOUND_ROWS with FOUND_ROWS(), but this will defeate the purpose of the LIMIT clause -- speed -- because MySQL will have to figure out all the results of the query

Re: Optimizing a big query...

If your tables are mostly read-only, you could pre-generate page numbers on a periodic basis and select only specific ranges WHERE row_number BETWEEN page_start AND page_finish. Or you could just send the top 1000 IDs of the table to the client, and have the client figure out which IDs belong to w

Re: Optimizing a big query...

Carlos, Apart from using the proper indexes, you should only retrieve the number of rows that are going to be displayed on the page at one time. If you are displaying 10 rows on the page, why retrieve 1000 rows? The user would have to page through it 100x and you have to ask yourself, is

Re: Optimizing a big query...

That's incredibly slow. I pull 1000 records through PHP in 1 second on a P4 2.4 Ghz. Are you pulling the entire recordset and then limiting it in your app code? Or are you using a limit clause in the DB? Also, Peter's point about indexing might help. Without specific SQL examples, it's difficu

Re: Optimizing a big query...

Does the app display all 1000 rows at once? Does your app require all fields? Only retrieve what you need for the page. If the app displays all 1000 rows, it may remain slow depending on how you get them (order, group, function) and indexing.. Also, the link and disk may matter depending on the si