Gruzin Posted December 23, 2006 Share Posted December 23, 2006 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%' UNIONSELECT * FROM `Mater` WHERE `MatNameE` LIKE '%$trimmed%'UNIONSELECT * FROM `Mater` WHERE `MatNameR` LIKE '%$trimmed%' ORDER BY MatName LIMIT $from, $max_results"; $query2 = "SELECT * FROM `Mater` WHERE `MatName` LIKE '%$trimmed%' UNIONSELECT * FROM `Mater` WHERE `MatNameE` LIKE '%$trimmed%'UNIONSELECT * 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 loopecho "<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 loopwhile($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 Link to comment https://forums.phpfreaks.com/topic/31696-need-help-with-mysql-search/ Share on other sites More sharing options...
Gruzin Posted December 23, 2006 Author Share Posted December 23, 2006 * Bump * Link to comment https://forums.phpfreaks.com/topic/31696-need-help-with-mysql-search/#findComment-146914 Share on other sites More sharing options...
HuggieBear Posted December 24, 2006 Share Posted December 24, 2006 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.RegardsHuggie Link to comment https://forums.phpfreaks.com/topic/31696-need-help-with-mysql-search/#findComment-147266 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.