I am running the 1 website on my server. That website has a search facility.
When I search for a category named "apple" at that time its showing 10 results and I am getting search result page fine and fast. [RECORDS ARE PAGINATED 10 records per page]
Now, When I search for "orange" named category at that time its giving me an internal server 500 error. It's just because it's trying to pull 300000 records from the database. [After 2 mins I am getting this error on page]
So How can I resolve this issue? I have checked queries too and its all fine. I need to record faster with no errors like internal server.
Is there any way to resolve this?
Please help! Thank you....
Use LIMIT in your database queries to get only the number of rows you need, for example this will select the first 10 rows :
SELECT * FROM "table" WHERE something = "something" LIMIT 10
And you can also use an offset, this will select 10 rows from the 5th row :
SELECT * FROM "table" WHERE something = "something" LIMIT 5,10
More information in the official MySQL documentation.
If you need the total number of rows you can use COUNT :
SELECT COUNT(*) FROM "table" WHERE something = "something"
You can store your information in another table or field and actualize it on inserting and deleting items
or
if you don't want to change your code, you can implement a cron job, that will actualize it in an interval of time.