Need help: MYSQL query for search record

opdsimran

Hi,

Currently i am working on a query to work on a table having more then 24 lacs(24,00000) records till date.

In the page i wanrt to run a search which will bring a list of rec for a specific criteria. Also the listing will be done pagewise.

Being using paging i have to query the same table every time user is switching to another page.

Now the requirement is , I dun't want to query again the same huge table to bring the same resultset again & again..

Can anybody give a optimized solution for the above requirement.

nnichols

You should use the usual method of running the same query but with a different LIMIT clause.

As long as the table is correctly indexed it should be fine (it's not that many records).

gtibok

http://phpbuilder.com/columns/rod20001214.php3
here is a article regarding your question

opdsimran

Hi nnichols,

Thanks for ur effort..:-)

but sorry to say that u had not understand my prob or i may not have put my queries correctly...

In sort issue is that i dun want to query the database again & again for every page..

Is their any method u can tell me so that i have to just query once to bring all the required rec and later display it pagewise..

Regards
OM

gtibok

You can store the results in array but you'll need lotta RAM or you can store in a temp file but it's more slower than SQL, so you should query the database eatch time. That's what I think

nnichols

I understand your question but I can't see any problem with running the query for each page.

If you retrieve all records and then store them in some form of cache file it does away with the problem of constantly requerying the db but retrieving the results from the cache file will be slow, defying the point in not requerying.

The other suggestion from "gtibok" of using an array is not feasible because of the memory requirements. Fine if you only have a couple of people browsing at the same time, but with heavy loads you'll just kill your server.

You should query the db each time. It is much quicker than using a cache file. You are not querying a huge number of records. So as long as your table indexes are efficient you'll have no problems. The db server is the most powerful tool you have - so use it to your advantage.

The only other possible option would be to use temporary heap tables, but it would not be appropriate for this.

Hope this make sense 😉

opdsimran

Hi buddy...,

I really apreciate for the effort u guys had put in..

Yes nnichols i am convinced with ur statement and will also implement the thing u had told me....:-)

Also i am thankful to gtibok..:-)

Hope in future also we will discuss some interesting stuff abt PHP and MYSQL..

Thanks
OM

zudo

you could put quieries in a fuction that is in a include file than it's all amatter of putting you include file and function in the script