I'm trying to write mysql query for advanced search of my website, the website is classified website and I'm trying to filter the ads, I have:
(`categoryID` = 53 OR `categoryID` = 54 OR `categoryID` = 141)
AND `countryID` = 8
AND `ad_active` = 1
AND `id` IN (
WHERE (`fieldID` = 10 AND `optionID` = 33) AND `useradID` IN (
WHERE (`fieldID` = 8 AND `optionID` = 15)
You query is particularly slow because you are using nested queries in the WHERE clause, which means the query may be reexecuted for each row evaluation. You do that twice in cascade, so it does a lot, lot, of extra computation.
You need to use MySQL JOIN: http://dev.mysql.com/doc/refman/5.0/en/join.html
SELECT a.* FROM t_userads a JOIN t_useradsvals v1 ON a.id = v1.useradID JOIN t_useradsvals v2 ON v1.useradID = v2.useradID WHERE a.categoryID IN (53, 54, 141) AND a.countryID = 8 AND a.ad_active = 1 AND v1.`fieldID` = 10 AND v1.`optionID` = 33 AND v2.`fieldID` = 8 AND v2.`optionID` = 15;
And define a index for (useradID, fieldID, optionID).
If you just want 1 or a few ads, you can use LIMIT at the end of the query: http://dev.mysql.com/doc/refman/5.0/en/select.html
Also, don't query *: Why is SELECT * considered harmful?