CODEWITHSUNDEEP
phpmysql
Tyler
Tyler

Reputation: 678

Optimize this Query in PHP

First of all, I have no control over the database structure, etc.

I need to use PHP to retrieve records by state and name but all the data is in multiple tables. Basically I need to try and understand how to get these two queries combined so that they do not run so slow.

First I get my record ID's in PHP. (Lets assume $query returns an array of IDs)

table_products = A bunch of products

$query = "SELECT id,name FROM table_products WHERE name = '".$name."';";

Than I need to iterate through these records (NOTE : There can be A LOT) and figure out where these IDs reside in another two tables that has the location information of where they could be at.

table_places = a table with a bunch of locations

link_table = Contains the relationships between product and location

$state = "somestate";

foreach($query as $row)
    {
    $query_two = "SELECT table_places.name, table_places.id, table_places.state, link_table.place_id, link_table.product_id 
    FROM table_places 
    INNER JOIN link_table 
    ON table_places.id = link_table.place_id 
    WHERE table_places.state = '".$state."' AND link_table.product_id = '".$row->id."';";
    }

God I hope this made sense. I am no query guru so if I could get assistance in optimizing this to run faster, I would be grateful.

Upvotes: 0

Views: 99

Answers (3)

giorgio
giorgio

Reputation: 10202

You can join more than two tables in one query. Untested query would be something like;

SELECT * FROM table_products AS prod LEFT JOIN (link_table AS link, table_places AS places)
    ON (prod.id=link.id AND prod.id=places.id)
WHERE some_field='some_value'

This will definitely get you some performance boost, as one query is most of times a lot faster than number-of-records query's (as you now loop through the records and query the db once per record)

Upvotes: 0

Johan
Johan

Reputation: 76537

The pain is here:

foreach($query as $row)   <<--- you are pinging the DB to death.

Combine the two queries:

SELECT 
  pl.name, pl.id, pl.state, 
  l.place_id, l.product_id,
  pr.name   
FROM table_places pl  
INNER JOIN link_table l ON (pl.id = l.place_id)  
INNER JOIN table_products pr ON (l.product_id = pr.id)
WHERE pr.name = '$name'
  AND pl.state = '$state'
ORDER BY pr.name, pl.state

Make sure you put indexes on all fields used in the ON clauses and the where clauses.

Upvotes: 4

Your Common Sense
Your Common Sense

Reputation: 157828

The answer is simple: no control over database structure - no way to optimize.
As the indexing being the cornerstone of query optimization and you obviously need access to table structure to add one.

Upvotes: -2

Related Questions