Minimizing mysql queries

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
LucosidE
Forum Newbie
Posts: 10
Joined: Fri Sep 25, 2009 9:56 am

Minimizing mysql queries

Post by LucosidE »

Hello :)

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
 
    }
 
}
 
If this is a good way of doing it, i found a work around where by having 3 database users and using a random user for the query, that increases the max_questions to 150000 per hour
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Minimizing mysql queries

Post by Eran »

Instead of querying inside a loop, perform another join to the specificities table and get all the information you need in one query.
[sql]SELECT L_ID, PL_NAME, U_USER_NAME, L_VERIFIED,specificities.* FROM linksINNER JOIN promoter_locus ON PL_ID = L_PROMOLOCUSINNER JOIN users ON U_ID = L_AUTHORINNER JOIN specificities ON specificities.spec_link = links.L_IDWHERE PL_NAME LIKE 'CA%';[/sql]
LucosidE
Forum Newbie
Posts: 10
Joined: Fri Sep 25, 2009 9:56 am

Re: Minimizing mysql queries

Post by LucosidE »

Thanks :)

Say I want to sort the results by U_USER_NAME or PL_NAME?
If i join the specificities and just loop through the result the
links wont be in order after i sort since one user may have more
than one entry.

here is what happens after i order by U_USER_NAME

Code: Select all

 
SELECT L_ID,PL_NAME,U_USER_NAME,L_VERIFIED,SPEC_ID FROM links
JOIN promoter_locus ON PL_ID = L_PROMOLOCUS
JOIN users ON U_ID = L_AUTHOR
JOIN specificities ON SPEC_LINK = L_ID 
ORDER BY U_USER_NAME;
 
L_ID       U_USER_NAME    SPEC_ID
35167     Brad*****        1
35155     Brad*****        3
35167     Brad*****        2
35155     Brad*****        4
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Minimizing mysql queries

Post by Eran »

I'm not sure what order you want it to be, but you can use several columns to order your results. For example: ORDER BY L_ID,U_USERNAME might give you the order you require.
LucosidE
Forum Newbie
Posts: 10
Joined: Fri Sep 25, 2009 9:56 am

Re: Minimizing mysql queries

Post by LucosidE »

I did not know that :oops:

Thanks a lot its great now,
if i run into any more problems ill be back :)
LucosidE
Forum Newbie
Posts: 10
Joined: Fri Sep 25, 2009 9:56 am

Re: Minimizing mysql queries

Post by LucosidE »

And im back 8O

Is it possible to count the number of links and for each link the number of specificities ??
Before i used
count(DISTINCT SPEC_ID) GROUP BY L_ID to count specificities
and $result->num_rows to count links

i can loop and put the result into an array before displaying and then user sizeof()
but that would make pretty big arrays, each link has about 10 specificities.

Here is the query to get everything at once,
SELECT
#ENTRY STUFF TO DISPLAY#
L_ID,PL_NAME,U_USER_NAME,L_VERIFIED
#SPECIFICITY STUFF TO DISPLAY#
,SPEC_ID,STG_NAME, AEX_NAME,AEX_IMAGE,
CEX_NAME,CEX_IMAGE, CT_NAME, APO_FULL_PATH

FROM links

Code: Select all

 
JOIN promoter_locus ON PL_ID = L_PROMOLOCUS
LEFT JOIN users ON U_ID = L_AUTHOR
LEFT JOIN comments ON C_ID = L_AUTHOR_COMMENT
#ADD SPECIFICITIES#
JOIN specificities ON SPEC_LINK = L_ID
  JOIN ana_stage ON STG_OID = SPEC_STAGE
  JOIN spec_map_area_ex ON SAEX_MAP_SPEC = SPEC_ID
  JOIN area_excision ON AEX_ID = SAEX_MAP_AEX
  JOIN spec_map_cell_ex ON SCEX_MAP_SPEC = SPEC_ID
  JOIN cell_excision ON CEX_ID = SCEX_MAP_CEX
  JOIN cell_type ON CT_ID = SPEC_CELL_TYPE
  JOIN anad_part_of ON APO_OID = SPEC_ANATOMY_PATH
ORDER BY L_ID;
 
php will add WHERE clause
and U_USER_NAME asc or PL_NAME asc between ORDER BY and L_ID
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Minimizing mysql queries

Post by Eran »

You can't count those both using the same query, probably. You can try COUNT(DISTINCT ...) on the unique fields you want to count (SPEC_ID and L_ID probably), otherwise you'd need to use a separate query for counting. It would still be only two queries as opposed to querying inside the loop which is far worse. COUNT queries are relatively very fast if they don't include additional fields.
Post Reply