Inner Join slowing me down?

[amg182]

Hi guys.

 

I have a SQL query running within my php. But if i add and inner join to link another table, it slows the performance(refresh, loading time) of the page by at least 2-3 seconds... I am working from localhost wampp serever. Should this happen when using inner Join?

 

$sql="SELECT * FROM cars INNER JOIN postcodes ON postcodes.Pcode=cars.Location where id>='1'"

 

Thanks in anticipation

[AyKay47]

joins are meant to conserve memory.. doesn't make much sense that this would be slowing you down.. sounds like it could have something to do with code elsewhere.. you can run a code to see how much memory this is actually allocating..

 

<?php

print memory_get_usage() . "<br />";

$sql="SELECT * FROM cars INNER JOIN postcodes ON postcodes.Pcode=cars.Location where id>='1'"
$join = mysql_query($sql);
if($join){
      print memory_get_usage();
}

?>

[DavidAM]

1) Using SELECT * will return all the columns from both tables for every matched row. The PHP engine has to buffer this data, so if there are a lot of columns, this could reduce through-put. It is almost always best to select only the columns that you will need.

 

2) Run the query directly in the database and see if the "slow down" shows up there.

 

3) Is postcodes.Pcode indexed? Is cars.Location indexed? Are both of those columns the same datatype? If one or the other is not indexed, you will end up doing a table scan to find all of the records. If the datatypes are different, the server is having to do a bunch of data conversions.

 

4) Use EXPLAIN to see what the query is doing -- what indexes are used, how many rows are scanned, etc. Post the results and we can talk about it.

 

5) It could be that your PHP processing of the results is causing a problem (array copies, etc). Post a little more of the code and we can see if we can help.

[Psycho]

memory_get_usage() may not tell the whole story since it only shows memory usage by PHP , not the database. But, it may be useful nonetheless.

 

Just out of curiosity, what is the purpose of the WHERE condition. Primary key IDs typically start at 1, do you actually have IDs that are less than 1?

 

In addition to the suggestions by DavidAM, are you sure you aren't running the query (or any other queries) in a loop?

 

[AyKay47]

memory_get_usage() may not tell the whole story since it only shows memory usage by PHP , not the database. But, it may be useful nonetheless.

 

Just out of curiosity, what is the purpose of the WHERE condition. Primary key IDs typically start at 1, do you actually have IDs that are less than 1?

 

In addition to the suggestions by DavidAM, are you sure you aren't running the query (or any other queries) in a loop?

true, the solution that i provided would be used to check the PHP side of things.. however if PHP is not the issue here, a mysql check will also need to be put into place to fully understand what is going on..

and as mjdamato suggested, i suspect a loop here as well..

[amg182]

Thanks a million guys, some really useful information here. I will look into running the query from the database and see how it goes. Will post back results!

 

mjdamato: I am using the where clause just for testing reasons. I meant to remove it before posting.

 

Thanks again

[amg182]

Hi guys.

 

Pleased to report back that by indexing the columns and making sure field types were the same worked a treat!(Thanks DavidAM)

 

Before indexing

 

Query took 0.0897 sec)

 

  after indexing

 

Query took 0.0011 sec)

 

This has restored the performance of the page back to its good old speedy self.

 

Guys you are a credit to this forum, thank you!