Mhd.Jarkas Mhd.Jarkas - 5 months ago 13
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

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.

Answer

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?

Comments