Page 1 of 1

Slow query - Any suggestions?

Posted: Tue Jul 02, 2002 2:05 am
by Hebbs
I am having problems with the following query, in particular with response times. It works well but is just slow.

My application is running on Apache and using MYSql.

In particular as the mysql database gets larger the query is getting slower (hmmmmm), I would appreciate it if someone can spot anything obviously wrong with the code.

Code: Select all

$query = "SELECT dat_a_mainrequest.AAA_Nr,RequestDate,list_reqstatus.RequestStatus,RequestType,Urgency,Offence,Commodity,RequesterRefNr,OR_Number,Reqperson_ID,Enqperson_ID,Surname,Given,OperationName,Caveat,RequestType,IFNULL(dat_tablea.SubEnqNr,dat_tableb.SubEnqNr)
   				FROM dat_a_mainrequest,type_rqst,list_requester,user_profile,dat_tablea,dat_tableb
				LEFT JOIN list_projects ON dat_a_mainrequest.Operation_ID = list_projects.Operation_ID 
				LEFT JOIN list_reqstatus ON dat_a_mainrequest.ReqStatus_ID = list_reqstatus.ReqStatus_ID 
				WHERE dat_a_mainrequest.AAA_Nr = '$AAAnum'
				AND (dat_tablea.SubEnqNr = '$subenqnr' OR dat_tableb.SubEnqNr = '$subenqnr')
				AND dat_a_mainrequest.ReqType_ID = type_rqst.ReqType_ID 
				AND dat_a_mainrequest.Requestlocation_ID = list_requester.Requestlocation_ID
				AND dat_a_mainrequest.Reqperson_ID = user_profile.User_ID
				AND (dat_a_mainrequest.AAA_Nr = dat_tablea.AAA_Nr OR dat_a_mainrequest.AAA_Nr = dat_tableb.AAA_Nr)";
Regards

Hebbs

Posted: Wed Jul 03, 2002 4:04 am
by Hebbs
OK then,

From the lack of response I can deduce the following:

a) This is absolutely perfect, or

b) No one can make sense of it!

I like the former, Ive always been delusional!

Hebbs

Posted: Tue Jul 16, 2002 2:07 am
by Hebbs
OK,

I have solved this two ways.

One was to break the query into two queries, removing the necessity for IFNULL and OR components.

The second was by reviewing my indexes. I didnt really have a great understanding of indexes in MYSql but after some research can now offer the following couple of simple Index guides:

1. Only Index the columns mentioned in your FROM section of your query (eg ...SELECT * FROM columna.tablea, columnb.tableb etc WHERE ...)

2. Index columns involved in LEFTJOINS

3. At dos use the 'explain' command to give a siummary of your query performance. (eg mysql> explain SELECT * FROM columna.tablea, columnb.tableb etc WHERE ...;)

Particularly the last has proven invaluable and returns a table detailing the tables, their types, keys or indexes avaialable for this query, key or indexes in fact used, key length, reference, rows and extra info.

If your indexes are providing best benefit the rows result in the explain should be one, ie only one row searched.

Try this its very handy for tweaking your tables.

Hebbs