Need Help with Mysql Search

[Gruzin]

Hi guys, hope you are doing well,
I'am working on a project where the database contains 200,000 records. The thing is that there is 8 tables with bad relations, I mean if I want to pull out some result, I need to join several tables. As you've guessed this takes a lot of time (3-4 min to show the result). 
1.How can I Index tables and use them (indexes)?
2. Is there any better way to join tables?

Here is my code (part of it):

[code]$query =
"SELECT *  FROM `Mater` WHERE `MatName` LIKE '%$trimmed%'
UNION
SELECT *  FROM `Mater` WHERE `MatNameE` LIKE '%$trimmed%'
UNION
SELECT *  FROM `Mater` WHERE `MatNameR` LIKE '%$trimmed%' ORDER BY MatName LIMIT $from, $max_results";

$query2 =
"SELECT *  FROM `Mater` WHERE `MatName` LIKE '%$trimmed%'
UNION
SELECT *  FROM `Mater` WHERE `MatNameE` LIKE '%$trimmed%'
UNION
SELECT *  FROM `Mater` WHERE `MatNameR` LIKE '%$trimmed%'";

$result = mysql_query($query) or die(mysql_error());
$num = mysql_num_rows($result);

$result2 = mysql_query($query2) or die(mysql_error());
$num2 = mysql_num_rows($result2);

$total_results = $num2;

echo "<strong>$num2 $echo</strong><br>"; // show the number of matching results before loop
echo "<a href='geo.php'> $back </a><p>";

$count = (($max_results*$page)-$max_results)+1;

if($num > 0){

$bgcolor = "#FOD3C8";
echo "<table border='0' width='100%' cellpadding='1' cellspacing='1'>
<tr>
<td bgcolor='#D27E59' align='left'><strong>N:</strong></td>
<td bgcolor='#D27E59' align='left'><strong>Name</strong></td>
<td bgcolor='#D27E59' align='left'><strong>Factory</strong></td>
<td bgcolor='#D27E59' align='left'><strong>Country</strong></td>
<td bgcolor='#D27E59' align='left'><strong>Price</strong></td>
<td bgcolor='#D27E59' align='left'><strong>Apt</strong></td>
<td bgcolor='#D27E59' align='left'><strong>Generic</strong></td>
<td bgcolor='#D27E59' align='left'><strong>Farm Group</strong></td>
</tr>";


// start loop
while($row = mysql_fetch_assoc($result)){

// Generic
$g_id = $row['GenID'];
$generic = mysql_query("SELECT * FROM Generics WHERE GenID=$g_id");
while($gen = mysql_fetch_array($generic)){
$gen_id = $gen['Generic'];
$gen_id2 = $gen['GenID'];
}
// price
$p_id = $row['MatID'];
$price = mysql_query("SELECT * FROM DrugRemainder WHERE MatID=$p_id");
while($pri = mysql_fetch_array($price)){
$pri_id = $pri['Price'];
$drug_id_check = $pri['DrugID'];
}
// farm group
$farm_id = $row['MatID'];
$farma = mysql_query("SELECT * FROM MatFarm,FarmGroups WHERE (MatFarm.MatID=$farm_id AND FarmGroups.FarmID=MatFarm.FarmID)");
while($far = mysql_fetch_array($farma)){
$far_id = $far['FarmName'];
$far_id2 = $far['FarmID'];
}
// quontity
$apt_id = $row['MatID'];
$apt = mysql_query("SELECT * FROM DrugRemainder,Drugstores WHERE (DrugRemainder.MatID=$apt_id AND Drugstores.DrugID=DrugRemainder.DrugID)");
while($apt_e = mysql_fetch_array($apt)){
$apt_r = $apt_e['DrugName']."<br>";
$apt_r2 = $apt_e['DrugAddr']."<br>";
$apt_r3 = $apt_e['DrugPhone'];
}
// factory
$fact_id = $row['MatID'];
$fact = mysql_query("SELECT * FROM DrugRemainder,Factory WHERE (DrugRemainder.MatID=$fact_id AND DrugRemainder.FactID=Factory.FactID)");
while($fact_e = mysql_fetch_array($fact)){
$fact_r = $fact_e['FactName'];
}
// country
$coun_id = $row['MatID'];
$coun = mysql_query("SELECT * FROM DrugRemainder,Factory,Country WHERE (DrugRemainder.MatID=$coun_id AND DrugRemainder.FactID=Factory.FactID AND Factory.CounID=Country.CounID)");
while($coun_e = mysql_fetch_array($coun)){
$coun_r = $coun_e['Country'];
}

if ($bgcolor == "#FOD3C8")
{
$bgcolor = "#F5E3DC";
}
else
{
$bgcolor = "#FOD3C8";
}

if(strlen($drug_id_check) < 1){
$apt_r = "0";
$apt_r2 = "";
$apt_r3 = "";
$pri_id = "0";
$fact_r = "";
$coun_r = "";
}

if(strlen($row['MatName']) > 0){
$res = $row['MatName'];
}
elseif(strlen($row['MatNameR']) > 0 ){
$res = $row['MatNameR'];
}
elseif(strlen($row['MatNameE']) > 0){
$res = $row['MatNameE'];
}
//--- start the result echo
echo "<tr>";
echo "<td bgcolor='$bgcolor' width='30'>$count</td>";
echo "<td bgcolor='$bgcolor'><a href='drug_det.php?drug_id=$p_id' target='_blank'>$res</a></td>";
echo "<td bgcolor='$bgcolor'><a href='fact_det.php?drug_id=$p_id' target='_blank'>$fact_r</a></td>";
echo "<td bgcolor='$bgcolor'><a href='coun_det.php?drug_id=$p_id' target='_blank'>$coun_r</a></td>";
echo "<td bgcolor='$bgcolor'>$pri_id</td>";
echo "<td bgcolor='$bgcolor'>$apt_r$apt_r2$apt_r3</td>";
echo "<td bgcolor='$bgcolor'><a href='gen_det.php?drug_id=$gen_id2' target='_blank'>generci</a></td>";
echo "<td bgcolor='$bgcolor'><a href='farm_det.php?drug_id=$far_id2' target='_blank'>farm Group</a></td>";
echo "</tr>";
$count ++;
//------- end results, end loop
}
  }
echo("</table>");[/code]

Thanks for you time and for your help (I really need that)!
George

[Gruzin]

* Bump *

[HuggieBear]

I hate to be the bearer of bad news but I think you're going to need to go through the process of 'Normalizing' your database first.  Then knowing where to put indexes will become a lot clearer.

This is more a MySQL question than PHP, perhaps you could get the topic moved to that forum.

Regards
Huggie