CODEWITHSUNDEEP
phpmysqlsqlperformance
Peter Olson
Peter Olson

Reputation: 142939

Is there a faster way to get information from multiple tables?

Let's suppose I have seven tables, and I want to perform the same query on all of them, and then return the result. Right now I have some code like this

$dates = array();
foreach ($tables as $table) {
  $result = runStatement("
    SELECT MIN(StartDate) as Start, 
           MAX(EndDate) as End 
           FROM $table WHERE ProjectID = ?",
    array($id));
  $stageDates[$table] = $result[0];
}
echo json_encode($dates);

It works, but it is prohibitively slow because it has to initiate and retrieve results from seven different queries. Is there a way to amalgamate all of the queries into one? Is there any other way to speed it up?

Upvotes: 0

Views: 121

Answers (3)

chris
chris

Reputation: 1255

If you have seven tables with 'StartDate', 'EndDate' and 'ProjectID', you could use materialized views (http://www.mysqlperformanceblog.com/2011/03/23/using-flexviews-part-one-introduction-to-materialized-views/) or index the data with Solr or Sphinx. The queries then are much faster, with the drawback that your data can outdate. If a few seconds or minute are no problem take a look at those solutions.

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Using join with subquery will solve the problem

SELECT
  MIN(t1.StartDate) as Start,
  MAX(t1.EndDate) as End,
  t2.Start,
  t2.END,
  t3.Start,
  t3.END  
FROM table1 as t1
  left join (SELECT
           MIN(t2.StartDate) as Start,
           MAX(t2.EndDate) as End
         FROM table1) as t2
    on t1.id = t2.id
  left join (SELECT
           MIN(t3.StartDate) as Start,
           MAX(t3.EndDate) as End
         FROM table1) as t3
    on t1.id = t3.id  
    -- and so on --  
WHERE t1.ProjectID = 1

Upvotes: 1

Peon
Peon

Reputation: 8020

Never ever put a query inside loop. That's one of the worst things to do.

I don't think you will get a faster way, but, as Dagon said, union will be a lot cleaner and safer way.

SELECT MIN(StartDate) as Start, MAX(EndDate) as End FROM $table1, $table2, $table3 WHERE ProjectID = ?",

Upvotes: 0

Related Questions