ASP Classic ORDER BY in MySQL Query crashing page

When I add ORDER BY deals.DEAL_ID before LIMIT the page times out and never loads. It works perfect without it. Please HELP !!!

Set ors = oConn.Execute("SELECT deals.DEAL_ID, deals.EMP_ID, users.fname AS empfname, users.lname AS emplname, users.usrcomm AS empdesc, deals.CLOSER_ID, userz.fname AS clsrfname, userz.usrcomm AS clsrdesc, userz.lname AS clsrlname, deals.split_comm, deals.EMP_LOC, deals.DEAL_DATE, deals.fname, deals.midname, deals.spfname, deals.splname, deals.lname, deals.address1, deals.address2, deals.city, deals.state, deals.zipcode, deals.phone1, deals.phone2, deals.email_addy, deals.pmnt_type, deals.cc_name, deals.card1, deals.card2, deals.card3, deals.card4, deals.card_exp_mo, deals.card_exp_yr, deals.cid_id, deals.bank_name, deals.check_numb, deals.route_numb, deals.acct_numb, deals.app_code, deals.deal_amt, deals.processthru, deals.resortcomp, deals.pkgname, deals.pri_location, deals.tripdetail, deals.pkgcost, deals.rmtype, deals.gift1, deals.gift2, deals.gift3, deals.gift4, deals.gift5, deals.gift6, deals.deal_status, deals.assigned_to AS prev_status, deals.CONF_PREFIX AS conf_code, deals.verify_date, users_1.UID AS verifid, users_1.fname AS vfname, users_1.lname AS vlname, deals.pmnt_date, deals.shdate, deals.cancel_date, deals.cancel_wk, deals.payroll_wk, deals.chargeback_wk, deals.mgr_cb_wk, deals.clsr_cb_wk, deals.pay_amt, deals.clsr_amt, deals.mgr, userz_1.fname AS mgrfname, userz_1.lname AS mgrlname, deals.go_green, deals.mail_sent, deals.deal_printed, deals.deal_issue, deals.wk_tag, deals.mnth_tag, deals.yr_tag, deals.deal_time, deals.vartime, deals.payshift, deals.dfield1, userz_2.fname AS hbfname, userz_2.lname AS hblname, deals.dfield2, deals.dfield3, deals.dfield4, deals.flagit, deals.source FROM ((((deals LEFT JOIN userz ON deals.CLOSER_ID = userz.UID) LEFT JOIN userz AS userz_1 ON deals.mgr = userz_1.UID) LEFT JOIN userz AS userz_2 ON deals.dfield1 = userz_2.UID) LEFT JOIN users AS users_1 ON deals.varifid = users_1.UID) LEFT JOIN users ON deals.EMP_ID = users.UID WHERE (((deals.deal_status) <> ‘Delete’)) LIMIT " & Session(“pgsz”) & “”)

Is deals.deal_id indexed? How many rows are you dealing with?

It is the primary key there are currently 18000 rows

That shouldn’t be enough to cause a timeout, unless the timeout is really short.

You could make this query a subquery and order that

SELECT * 
  FROM (SELECT deals.DEAL_ID
			 , deals.EMP_ID
			 , users.fname AS empfname
			 , users.lname AS emplname
			 , users.usrcomm AS empdesc
			 , deals.CLOSER_ID
			 , userz.fname AS clsrfname
			 , userz.usrcomm AS clsrdesc
			 , userz.lname AS clsrlname
			 , deals.split_comm
			 , deals.EMP_LOC
			 , deals.DEAL_DATE
			 , deals.fname
			 , deals.midname
			 , deals.spfname
			 , deals.splname
			 , deals.lname
			 , deals.address1
			 , deals.address2
			 , deals.city
			 , deals.state
			 , deals.zipcode
			 , deals.phone1
			 , deals.phone2
			 , deals.email_addy
			 , deals.pmnt_type
			 , deals.cc_name
			 , deals.card1
			 , deals.card2
			 , deals.card3
			 , deals.card4
			 , deals.card_exp_mo
			 , deals.card_exp_yr
			 , deals.cid_id
			 , deals.bank_name
			 , deals.check_numb
			 , deals.route_numb
			 , deals.acct_numb
			 , deals.app_code
			 , deals.deal_amt
			 , deals.processthru
			 , deals.resortcomp
			 , deals.pkgname
			 , deals.pri_location
			 , deals.tripdetail
			 , deals.pkgcost
			 , deals.rmtype
			 , deals.gift1
			 , deals.gift2
			 , deals.gift3
			 , deals.gift4
			 , deals.gift5
			 , deals.gift6
			 , deals.deal_status
			 , deals.assigned_to AS prev_status
			 , deals.CONF_PREFIX AS conf_code
			 , deals.verify_date
			 , users_1.UID AS verifid
			 , users_1.fname AS vfname
			 , users_1.lname AS vlname
			 , deals.pmnt_date
			 , deals.shdate
			 , deals.cancel_date
			 , deals.cancel_wk
			 , deals.payroll_wk
			 , deals.chargeback_wk
			 , deals.mgr_cb_wk
			 , deals.clsr_cb_wk
			 , deals.pay_amt
			 , deals.clsr_amt
			 , deals.mgr
			 , userz_1.fname AS mgrfname
			 , userz_1.lname AS mgrlname
			 , deals.go_green
			 , deals.mail_sent
			 , deals.deal_printed
			 , deals.deal_issue
			 , deals.wk_tag
			 , deals.mnth_tag
			 , deals.yr_tag
			 , deals.deal_time
			 , deals.vartime
			 , deals.payshift
			 , deals.dfield1
			 , userz_2.fname AS hbfname
			 , userz_2.lname AS hblname
			 , deals.dfield2
			 , deals.dfield3
			 , deals.dfield4
			 , deals.flagit
			 , deals.source 
		  FROM deals 
		  LEFT JOIN userz ON deals.CLOSER_ID = userz.UID 
		  LEFT JOIN userz AS userz_1 ON deals.mgr = userz_1.UID
		  LEFT JOIN userz AS userz_2 ON deals.dfield1 = userz_2.UID
		  LEFT JOIN users AS users_1 ON deals.varifid = users_1.UID
		  LEFT JOIN users ON deals.EMP_ID = users.UID 
		 WHERE deals.deal_status <> 'Delete'
		 LIMIT 100)
 ORDER BY deal_id

I think this is progress.
Microsoft OLE DB Provider for ODBC Drivers error ‘80004005’

[MySQL][ODBC 3.51 Driver][mysqld-5.1.73-community]Every derived table must have its own alias

I fixed that error. You are awesome. But When I sort Desc it is showing me 100 to 1 Not 18000 to 17900

Ah, my fault - should have thought of that.

This article could be of interest to you - it seems the joins cause the indexes to be odd…

I’m sure @r937 will have a better idea, but you could put the query itself in a view, then do a SELECT with the limit on the view.

Thank you for all your help I am back on Track

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.