Write Mysql query for advanced search

Question

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:

t_userads
t_useradsvalues

the useradsvalues contains the userad values which the user choose, useradsvalues table structure:

ID
userAdID
fieldID
optionID

the fields and options are already defined in another tables.

ex: Need ads for BMW Car has color filed ID is 10 & Green option ID is 33 also doors field ID is 8 ans 4doors option ID is 15

now I tried to write

SELECT
  *
FROM `t_userads`
WHERE
  (`categoryID` = 53 OR `categoryID` = 54 OR `categoryID` = 141)
  AND `countryID` = 8
  AND `ad_active` = 1
  AND `id` IN (
    SELECT
      `useradID`
    FROM `t_useradsvals`
    WHERE (`fieldID` = 10 AND `optionID` = 33) AND `useradID` IN (
      SELECT
        `useradID`
      FROM `t_useradsvals`
      WHERE (`fieldID` = 8 AND `optionID` = 15)
    )
  )

I got the result right but the mysql query took about 6sec to execute (I have 5000 ads) and It's a problem because how it will take if I have 100,000 ads ?

Thank you.

Also consider using categoryId in (53, 54, 141). You can reference: http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance for empirical data. — aztechy, Aug 25 '13 at 05:17

score 1 · Accepted Answer · edited May 23 '17 at 12:33

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?

Write Mysql query for advanced search

1 Answers1