Looking for easier mysql code..It takes way too long to conclude.

[kickstart]

Hi

 

For the joins mentioned the indexes are more than adequate (I assume that you have other things that require all the indexes on the dealers tables).

 

As to cardinality, might be worth forcing a refresh using ANALYZE TABLE.

 

All the best

 

Keith

[don]

Thanks again keith for your help with this nightmare.  The indexes are used for other areas.  What if I do a query for all dealers that match the category first?  Then do a query on the results that match after calculations and other qualifications are true.  Is that possible in mysql.  Sounds easy.

 

So..would this be faster to do calculations

 

Select * as categoryresults from DCM

Where $category = DCM.categoryID

 

Select dealers

From categoryresults

 

Where (math and the rest )

 

 

[kickstart]

Hi

 

Doing 2 separate queries is generally far slower which is why you use a JOIN.

 

You could possibly use a subselect first to get a list of dealers, but I would expect this to perform worse as mysql is going to have to try to pass the indexes through.

 

However, how many matching categories are there? Could a dealer come back with dozens of rows, one for each matching category? Is the query mainly causing a problem when there is no category being checked?

 

If the category isn't specified then there is no point in doing the JOIN on the category and dealer_category_matrix tables. If the category is specified then it might be worth putting the check for that in the JOIN (to exclude them before any calculations are done).

 

In the code that is playing up what is zipcode zipcodesearch :-

 

$Dealer_Results->DataSource->SQL =

"SELECT distinct dealers. * , ( 3963 * acos( sin( '$rrr'/ 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( '$rrr' / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - '$sss' / 57.2958 )) )

AS Dealer_Distance

FROM ((dealer_category_matrix RIGHT JOIN dealers

ON dealers.Dealer_ID = dealer_category_matrix.DCM_Dealer_ID)

LEFT JOIN category

ON dealer_category_matrix.DCM_Cat_ID = category.Cat_ID)

INNER JOIN zipcode

ON zipcode.ZIPCode = dealers.Dealer_Zip, zipcode zipcodesearch

WHERE (dealers.Dealer_Zip = zipcode.ZIPCode)

AND (dealers.Dealer_Publish='Y')

AND (dealers.Dealer_Approved='Y')

AND (CURDATE() < dealers.Dealer_Expires)

AND (( 3963 * acos( sin( '$rrr' / 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( '$rrr' / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - '$sss' / 57.2958 ) ) ) <" . $Radius . ")

AND (dealers.Dealer_BusinessName LIKE '%" . $BusinessName . "%')

AND (dealers.Dealer_State = '" . $State . "' OR '0'='" . $State . "0')

AND (category.Cat_ID='" . $Category . "' OR ''='" . $Category . "')";

 

That looks like it is doing trying to possibly do a cross join on the zipcode table (giving it an alias name of zipcodesearch), and suspect that will result in a hideous number of records.

 

All the best

 

Keith

[don]

Great now im getting somewhere thanks again for your expert advise.  Originally I designed this website to search zip codes only not addresses that the user inputs.  So when I modified the user input fields to use addresses not zipcodes everything worked fine until I have too many dealer records.  The Zipcode table holds the zipcode with the corresponding  lat and long of the zipcode.  Since I am not using the zipcode portion for the search I think querying the zipcode table doesnt do anything for me so I will omitt it.  Now with that being said the user will only input a address and category selection.  I will also omitt the opprotunity to choose the state or name of business since they also seem worthless at this time.

 

Each dealer can have every category- say 100 different categories.  Now with only one input field for address and 1 select field for Category - of course only 1 category at a time, I should probably only query the category  table first. Like this

 

Select DCM_DealerID

From DCM

Where DCM_Category_ID=$category

Left Join Dealers

Where DCM_DEALERID = dealers.Dealer_ID

AND (dealers.Dealer_Publish='Y')

AND (dealers.Dealer_Approved='Y')

AND (CURDATE() < dealers.Dealer_Expires)

AND (( 3963 * acos( sin( '$rrr' / 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( '$rrr' / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - '$sss' / 57.2958 ) ) ) <" . $Radius . ")

 

 

 

What do you think is that logical ?

 

 

 

 

 

 

[kickstart]

Hi

 

Odd way of mixing the WHERE clause. I presume you meant

 

Select DCM_DealerID 
From DCM
Left Join Dealers 
ON DCM.DEALERID = dealers.Dealer_ID
Where DCM_Category_ID=$category
AND (dealers.Dealer_Publish='Y') 
AND (dealers.Dealer_Approved='Y') 
AND (CURDATE() < dealers.Dealer_Expires) 
AND (( 3963 * acos( sin( '$rrr' / 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( '$rrr' / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - '$sss' / 57.2958 ) ) ) <" . $Radius . ")

 

Your still need the zipcode table as you are getting the longitude and latitude of the dealer from it (and using it in that query).

 

Further you are doing a left outer join on the dealer matrix table. That will bring back every single row, most of which you then discard with the WHERE clause. You may as well use an INNER JOIN

 

Cleaning those issues up you get:-

 

Select DCM_DealerID 
From Join dealers 
INNER JOIN DCM
ON dealers.Dealer_ID = DCM.DEALERID
INNER JOIN zipcode 
ON dealers.Dealer_Zip = zipcode.ZIPCode
Where DCM_Category_ID=$category
AND (dealers.Dealer_Publish='Y') 
AND (dealers.Dealer_Approved='Y') 
AND (CURDATE() < dealers.Dealer_Expires) 
AND (( 3963 * acos( sin( '$rrr' / 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( '$rrr' / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - '$sss' / 57.2958 ) ) ) <" . $Radius . ")

 

Did you try your earlier code with , zipcode zipcodesearch removed?

 

All the best

 

Keith

[don]

Keith your the master.  I removed the zipcode as zipcodesearch and no more hangups.  Works perfectly and I already updated the website thanks for all your help. 

One more quick unimportant question.  I tried to incorporate the mysql_num_rows function as the example states

$a = mysql_num_rows ("select $#%$^%$") and it comes up with an error.  I tried the example with a generic query and it works.  I tried using the error code problem solving technique which gave me a number that I looked up and it still didnt show anything. Will mysql_num_rows work on the result set that i have or any result set that comes up so I can incorporate both of these queries.

 

I have a feeling that I am doing something wrong that is an easy fix.

Thanks again for your help.

[kickstart]

Hi

 

With mysql_num_rows() you just use the resource variable for the particular query (normally I do it immediately and store the result if I need to use it).

 

So:-

 

$sql = "SELECT * FROM table1";
$result = mysql_query($sql)
$num_rows = mysql_num_rows($result); 

 

Something like that should work (assuming the query worked).

 

All the best

 

Keith

[don]

Yeah that works just like you have it but when its used in this below it errors but codes working?

 

$sql2 = mysql_query("SELECT distinct dealers. * , ( 3963 * acos( sin( '$rrr'/ 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( '$rrr' / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - '$sss' / 57.2958 )) ) AS Dealer_Distance FROM dealers, zipcode as zipcode

WHERE (dealers.Dealer_Zip = zipcode.ZIPCode)

AND (dealers.Dealer_Publish='Y')

AND (dealers.Dealer_Approved='Y')

AND (CURDATE() < dealers.Dealer_Expires)

AND (( 3963 * acos( sin( '$rrr' / 57.2958 ) * sin( zipcode.latitude / 57.2958 ) + cos( '$rrr' / 57.2958 ) * cos( zipcode.latitude / 57.2958 ) * cos( zipcode.longitude / 57.2958 - '$sss' / 57.2958 ) ) ) <" . $Radius . ")

AND (dealers.Dealer_BusinessName LIKE '%" . $BusinessName . "%') AND (dealers.Dealer_State = '" . $State . "' OR '0'='" . $State . "0')");

 

$Dealer_Results->DataSource->SQL = $sql2;

$num_rows = mysql_num_rows($sql2);

 

[kickstart]

Hi

 

Not sure, but wondering what $Dealer_Results->DataSource->SQL = $sql2; is doing . You could try moving the mysql_num_rows above that line so it is immediately after the query.

 

All the best

 

Keith