Optimize this Query in PHP

Question

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.

Do you have indexes on your relevant columns? That alone could help speed up the queries significantly. — julio
– julio, Commented Nov 3, 2011 at 14:41
Unless I'm missing something can you not just write one query that joins all three queries together? — liquorvicar
– liquorvicar, Commented Nov 3, 2011 at 14:44
@liquorvicar : "I need to try and understand how to get these two queries combined", the OP says. So you're not missing anything, and I think you could answer the question. — greg0ire
– greg0ire, Commented Nov 3, 2011 at 14:48

Johan · Accepted Answer · 2011-11-03 14:47:15Z

4

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.

answered Nov 3, 2011 at 14:47

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler Over a year ago

I think this is what I was looking for. Going to try this. Be right back!

giorgio · Accepted Answer · 2011-11-03 14:56:59Z

0

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)

edited Nov 3, 2011 at 14:56

answered Nov 3, 2011 at 14:45

giorgio

10.2k2 gold badges30 silver badges41 bronze badges

2 Comments

Your Common Sense Over a year ago

you are wrong about "always". there are some things like query plan and stuff.

giorgio Over a year ago

maybe always is a bit too strong, but, assuming he has at least hundreds of records, in this case it'll be faster. There is a flipping point somewhere, depending on the weight of both types of query's, the number of query's etc.

Your Common Sense · Accepted Answer · 2011-11-03 14:42:47Z

-2

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.

answered Nov 3, 2011 at 14:42

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

8 Comments

Tyler Over a year ago

Ok, I am unfamiliar with indexing. Could this possibly be the solution?

Zelda Over a year ago

You can still optimize the query statement itself it is pulling too much data or not using the most optimal way of getting data. In this case I don't see anything wrong with the query to correct though.

N.B. Over a year ago

If you have no control over db structure then you can't index anything.

Your Common Sense Over a year ago

indexing is not "possibly be the solution", but, as I said, a cornerstone, an alpha and omega, the only way to optimize queries. a reference: dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

giorgio Over a year ago

@Col.Shrapnel "the only way"? Not exactly. First of all the query should be as 'light' as possible... But you should ALWAYS, if possible, index your tables. But in this case; indexing the table doesn't alter the structure of the database, so other code on the same database won't be affected.

|

Collectives™ on Stack Overflow

Optimize this Query in PHP

3 Answers 3

1 Comment

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related