3

I am facing a problem with my query ...

SELECT 
keywords.keyword,
keywords.keyid,
sources.s_title,
sources.s_disc,
sources.s_link,
sources.sourceid,
sources.s_link
FROM link_ks
INNER JOIN keywords ON link_ks.keyid = keywords.keyid
INNER JOIN sources ON link_ks.sourceid = sources.sourceid
INNER JOIN thumbsup_items ON link_ks.sourceid = thumbsup_items.name
WHERE link_ks.keyid = :keyid 
order by thumbsup_items.votes_up desc,thumbsup_items.votes_down asc,(thumbsup_items.votes_up+thumbsup_items.votes_down) desc

Every thing was good when database was small but as database become big I am getting this error

 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET
 SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

But when I remove

 order by thumbsup_items.votes_up desc,thumbsup_items.votes_down asc,
(thumbsup_items.votes_up+thumbsup_items.votes_down) desc

Problem gets solved ... But order by is important for this query ...

is there any other way to write this query or how can I solve this problem ??

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
Harinder
  • 1,257
  • 8
  • 27
  • 54

2 Answers2

4

the MAX_JOIN_SIZE and SQL_BIG_SELECTS won't let you run long queries which will hang the server.

Use this before running the query:

 SET OPTION SQL_BIG_SELECTS = 1

or use this:

SET SQL_BIG_SELECTS=1

chek this

Community
  • 1
  • 1
echo_Me
  • 37,078
  • 5
  • 58
  • 78
  • Thx it work but i have a question ... will this slow down the query or will it put load on server ? and is this safe to use ? – Harinder Mar 25 '14 at 10:02
  • this is just to let you run big query , nothing to do with safe to use or slowing down the query. the sharing hosts making having this to dont hang the server on long queries. – echo_Me Mar 25 '14 at 10:30
1

for those, like me, who are seeing this for the first time...

this =>

$setupMySql = mysql_query(" SET OPTION SQL_BIG_SELECTS = 1 " ) or die('Cannot complete SETUP BIG SELECTS because: ' . mysql_error());

Bill Warren
  • 392
  • 8
  • 11