I am wondering what is the better way to query the database when searching through it. I have 2 main tables "links" and "specificities" each link has more than one specificity, the search result should display link and its specificities. Right now i retrieve the link_ids loop through the result and query specificities for each link_id. the problem is that on the sever there is a limit for maximum queries per hour max_questions = 50000. I limit the result to 100 link_id, so the max number of queries that occurs during each result is 1 for link_id + 100 to get specificities for each link. That allows maximum of 495 searches per hour.
There are more queries occurring for adding a link to the database, commenting on a link, registering and so on.
Is there a better way to search through the database to minimize the number of queries ? How can I display the result using PHP ?
Here is an example of the search query i have right now
Code: Select all
//get links
SELECT L_ID, PL_NAME, U_USER_NAME, L_VERIFIED FROM links
JOIN promoter_locus ON PL_ID = L_PROMOLOCUS
JOIN users ON U_ID = L_AUTHOR
WHERE PL_NAME LIKE 'CA%';
while($row = $result->fetch_array){
//display id, author, and promoter
//get specificities for this link
SELECT * from specificities where spec_link = $row['L_ID']
//loop and display the specificities
while($foo = $specs->fetch_array())
{
//display specificities
}
}