What can I do to optimize my db ? (please look at EXPLAIN statement)

[2levelsabove]

EXPLAIN 
Select 
reg_users_ads.reg_users_ads_id, reg_users_ads.year, make, model, reg_users_ads.trim, reg_users_ads.price, reg_users_ads.exterior_color, reg_users_ads.transmission, reg_users_ads.mileage, zipcode 
FROM reg_users_ads, atrMakes, atrModels 
WHERE reg_users_ads.atrMakesID = '65' AND reg_users_ads.atrModelsID = '712' AND reg_users_ads.atrMakesID = atrMakes.atrMakesID AND reg_users_ads.atrModelsID = atrModels.atrModelsID AND reg_users_ads.zipcode IN ('75023','76888','76888','76888','78722') AND status = 'active';

 

 

 

the result being:

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE atrMakes const PRIMARY PRIMARY 4 const 1

1 SIMPLE atrModels const PRIMARY PRIMARY 4 const 1

1 SIMPLE reg_users_ads ALL NULL NULL NULL NULL 18971 Using where

[fenway]

First, your query should look like this:

 

SELECT
rua.reg_users_ads_id, rua.year, make, model, rua.trim, rua.price, rua.exterior_color, rua.transmission, rua.mileage, rua.zipcode
FROM reg_users_ads AS rua
INNER JOIN atrMakes AS ma ON ( rua.atrMakesID = ma.atrMakesID AND ma.atrMakesID = '65' )
INNER JOIN atrModels AS mo ON ( rua.atrModelsID = mo.atrModelsID AND mo.atrModelsID = '712' )
WHERE rua.zipcode IN ('75023','76888','76888','76888','78722') AND status = 'active';

 

You should always specify a table prefix ... like for zipcode and status (the latter of which I can't even guess.

 

But I get the feeling you have no indexes.

[Mchl]

So do I. Index on reg_users_ads.artMakesID and reg_users_ads.artModelsID could help.