Record Retrieval Limit in MySQL?

markmardon

I have a table with about 1300 records in it. I've noticed recently that when I retrieve all the records and then want to look at details of any single record, I get a "Page cannot be displayed error". If I delete/archive some of the older records and get below about 1200, everything works fine... until I add more records.

Any clue? Is there some record retrieval buffer or other setting that I can adjust to fix this problem, or do I need to re-write the code to limit the number of records retrieved? Or something else, maybe?

Thanks,

Mark

LordShryku

Could be your table setup...lack of indexes, overhead. Try optimizing.

Could also be your code. Unneccesary loops, getting stuck somewhere, timing out.

There's really no limit on the amount of records you can grab in a SQL query, and 1300 is really not many. The script may just be timing out. Try running the SQL query at the command line to see how long it takes there. If it's fast, try making a page that does nothing but execute this query.

markmardon

Hmmm... I'll give that a try and see what I can find.

The weird thing is that it seems to retrieve the list of records fine. No timeouts. It's when I try to view the details of any single one of those records (which have a unique record id --- which is also the primary key --- that I use as my select criteria so that I only retrieve that one) in that list that I run across the issue. And it happens immediately. No apparent lag between the request for details and the error page appearing.

Does that make sense?

LordShryku

So you're linking to a details page with the ID in the URL (like details.php?id=23)? And it normally works, but it's bringing up page not found when you pull all records? That sounds a little too odd. You sure the spelling is correct in the link? How's the code on the details page look?

markmardon

Yup. Exactly. It gets a little more bizarre... as I near the ~1300 record limit, I start to see the problem intermittently. As I add more records, the problem gets worse until it doesn't work at all.

When I begin to see the problem, I can actually refresh the details page by pressing <F5> once or twice and most times it'll bring up the record. However, that works less and less often as I get more records into the system.

As info, the details page just does a select on the single record using the record id. It's pretty straightforward, I think.

markmardon

All is good again. Thanks for your help.

Originally (before I knew better) I generated the php using Adobe Go-Live. As I've become more comfortable with php, I've been slowly getting rid of all the Go-Live specific references. Hadn't gotten round to this bit of code yet, but as soon as I re-wrote it without the Go-Live garbage, everything seemed to work (better, actually... much faster).

Again, thanks!