Mhd.Jarkas Mhd.Jarkas - 1 year ago 89
SQL Question

Write Mysql query for advanced search

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

FROM `t_userads`
(`categoryID` = 53 OR `categoryID` = 54 OR `categoryID` = 141)
AND `countryID` = 8
AND `ad_active` = 1
AND `id` IN (
FROM `t_useradsvals`
WHERE (`fieldID` = 10 AND `optionID` = 33) AND `useradID` IN (
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.

Answer Source

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:

FROM t_userads a
  JOIN t_useradsvals v1 ON = 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:

Also, don't query *: Why is SELECT * considered harmful?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download