suttercain Posted May 9, 2008 Share Posted May 9, 2008 Hi Everyone, I am currentley using the following code to query a database and through five tables <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <?php require('get_connected.php'); $sql = "SELECT * FROM device, device_eo, eo, eo_engine_family, engine_family WHERE device.device_id = device_eo.device_id AND device_eo.executive_order = eo.executive_order AND eo.executive_order = eo_engine_family.executive_order AND eo_engine_family.engine_family_name = engine_family.engine_family_name AND eo.decs_family_name = 'CA/CLE/2003/PM3/N25/ON/LNF02'"; $results = mysql_query($sql) or die(mysql_error()); ?> <table> <tr> <td>device_mfr</td> <td>device_id</td> <td>executive_order</td> <td>decs_family_name</td> <td>engine_family_name</td> </tr> <?php while ($row = mysql_fetch_array($results)) { ?> <tr> <td><?php echo $row['device_mfr'];?></td> <td><?php echo $row['device_id'];?></td> <td><?php echo $row['executive_order'];?></td> <td><?php echo $row['decs_family_name'];?></td> <td><?php echo $row['engine_family_name'];?></td> </tr> <?php } ?> </table> <body> </body> </html> It's a straight join, but seems to be going pretty slow. Is there a way to speed up the query? Thanks. SC Link to comment https://forums.phpfreaks.com/topic/104873-solved-mysql-query-through-five-tables-working-but-slow/ Share on other sites More sharing options...
BlueSkyIS Posted May 9, 2008 Share Posted May 9, 2008 are the fields in the query indexed? that might help. Link to comment https://forums.phpfreaks.com/topic/104873-solved-mysql-query-through-five-tables-working-but-slow/#findComment-536849 Share on other sites More sharing options...
suttercain Posted May 15, 2008 Author Share Posted May 15, 2008 Not sure what indexing is...?? Link to comment https://forums.phpfreaks.com/topic/104873-solved-mysql-query-through-five-tables-working-but-slow/#findComment-541837 Share on other sites More sharing options...
KenDRhyD Posted May 15, 2008 Share Posted May 15, 2008 If you want JOINs between tables to be efficient, the references should always us primary key fields. One can use indexed fields, but they are a bit slower, and non-indexed fields are very slow as they require a complete table scan for each row match. Consider the specific statement that you are building. The link between the [device] table and the [device_eco] table is via the [device_id] field. In order for this lookup to be as efficient as possible, [device_id] should be the primary key in the [device_eco] table. Similarly, the [device_eo] table is related to the [eo] table via the [executive_order] column, and so that column should be the primary key in the [eo] table. The same is true for the remaining relations. $sql = "SELECT * FROM device, device_eo, eo, eo_engine_family, engine_family WHERE device.device_id = device_eo.device_id AND device_eo.executive_order = eo.executive_order AND eo.executive_order = eo_engine_family.executive_order AND eo_engine_family.engine_family_name = engine_family.engine_family_name AND eo.decs_family_name = 'CA/CLE/2003/PM3/N25/ON/LNF02'"; Link to comment https://forums.phpfreaks.com/topic/104873-solved-mysql-query-through-five-tables-working-but-slow/#findComment-542061 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.