What will you do to optimize MySQL performance in this case?

Question

$query = "select * from a ...";
if($result = mysql_query($query,$con))
{while($row = mysql_fetch_array($result);)
  $arr[0][] = $row;
}
$query = "select * from b ...";
if($result = mysql_query($query,$con))
{
 while($row = mysql_fetch_array($result);)
  $arr[1][] = $row;
}
$query = "select * from c ...";
if($result = mysql_query($query,$con))
{
 while($row = mysql_fetch_array($result);)
  $arr[2][] = $row;
}

The 3 queries retrieves multiple rows with different structure, 3 queries need 3 round-trip, 10 queries will need 10 round-trip.

How will you optimize this case?

Answer 1

Select the columns you actually need instead of select *

Other than that, not much to say without knowing what data you need and how they relate.

Answer 2

You could try a UNION:

$query = "SELECT 'a' AS tablename, a.* FROM a
UNION
SELECT 'b' AS tablename, b.* FROM b
UNION
SELECT 'c' AS tablename, c.* FROM c";
if($result = mysql_query($query,$con))
{while($row = mysql_fetch_array($result);)

switch ($row['tablename']) {
    case "a":
        $arr[0][] = $row;
        break;
    case "b":
$arr[1][] = $row;
        break;
    case "c":
        $arr[2][] = $row;
        break;
}
}

pardon my php, it's not my area of expertise :)