Too complex - or just wrongly witten

trooper

I have an sql query (below) that i'd like feedback on - in particular is this an efficient way of doing it or am i missing another good way (oh and by the way subselects not supported in my mysql version)

"SELECT
	m.id,
	m.sent_date,
	IF(
	     m.sender_type ='bride' || m.sender_type ='groom',
	     CONCAT(ud.first_name,' ',ud.last_name),
	     gd.guest_names
	   ) 
	AS 'sender',
	m.sender_id,
	IF(
                     m.message='',
                     m.message_id,
                     m.message
                   ) 
                AS 'message'
FROM
	messages m,
	users u,
	users_details ud,
	guest_details gd,
	guest_logins gl
WHERE
	m.recipient_type ='{$user_type}'
AND
	m.recipient_id='{$user_id}'
AND
	m.read_state ='f'
AND
	m.wedding_id='{$wedding_id}'
AND
	IF(
 	    m.sender_type ='bride' || m.sender_type='groom',
	    u.id = m.sender_id  AND  ud.id = u.user_details_id,
	    m.sender_id = gl.guest_details_id AND  gd.id = gl.guest_details_id
	)
	GROUP by sent_date"

The reason i'm asking is that i'm not 100% sure of my if statements - when run the query does work - and work well but seems to be taking a tad too long for my liking.

Cheers

trooper

Ok then i'll have a conversation with myself (always reckon i was mad!!)

OK to sum up my issue was that the sql code in the previous post was nice and neat and returned everything i wanted from multiple tables - but it was too slow.

By too slow i mean it was taking 3.5 seconds to perform the lookup on a recordset of 3 records

Anyway i did a benchmark test on it using PEAR's timer class and sure enough it was pretty slow. Slow in comparison to what i hear ya think - well slow in comparison to re-designing and splitting the sql up into 2 smaller sql queries

Once i'd re-designed the sql into 2 smaller queries the time dropped from 3.17 for 3 returned records to less than 0.022070 of a second forf 15 records!!!

OK so its not a vast amount of record - but hey if this is the sort of saving that can be made on a small recordset - imagine the savings on larger ones?

So what is the point of this rant and rave - be careful when you use "if" statements in mysql queries (you can see that i've got 3 in the previous post)- i heard somewhere (can't remember where) that every "if" used in a mysql statement increases the time by the power of ^2. Now whether you believe that or not is up to you.

Final point - if you think that its taking to long to return your results from a my sql query then it probably is.

Don't be afraid to re-do your queries - believe me it is worth it.

HTH someone

jjoske

I've used if statements in with databases with something like a million records and have ok results. I would be having a closer look at your query and seeing what indexes it is using .

Bijan

Hi, sorry for interuption (!!), but what's this PEAR's class? How can I get it?