How can I optimize this query?

[edal11]

How can I optimize this query? It is taking 4.8362 sec (3 rows returned).

Here's the query:

SELECT DISTINCT my_favourites.id, my_favourites.type,
IF (
strcmp( my_favourites.type, 'House' ) ,
IF (
strcmp( my_favourites.type, 'Land' ) , buildings.address, lands.address
), houses.address
),
IF (
strcmp( my_favourites.type, 'House' ) ,
IF (
strcmp( my_favourites.type, 'Land' ) , buildings.price, lands.price
), houses.price
),
IF (
strcmp( my_favourites.type, 'House' ) ,
IF (
strcmp( my_favourites.type, 'Land' ) ,
IF (
strcmp( buildings.currency, 'D' ) , 'Lps.', 'US$'
),
IF (
strcmp( lands.currency, 'D' ) , 'Lps.', 'US$'
)
),
IF (
strcmp( houses.currency, 'D' ) , 'Lps.', 'US$'
)
), cities.name, states.name,
IF (
strcmp( my_favourites.type, 'House' ) ,
IF (
strcmp( my_favourites.type, 'Land' ) ,
IF (
strcmp( buildings.type_transaction, 'V' ) , 'Rental', 'Sale'
),
IF (
strcmp( lands.type_transaction, 'V' ) , 'Rental', 'Sale'
)
),
IF (
strcmp( houses.type_transaction, 'V' ) , 'Rental', 'Sale'
)
),
IF (
strcmp( my_favourites.type, 'House' ) ,
IF (
strcmp( my_favourites.type, 'Land' ) , 'buildings', 'lands'
), 'houses'
), my_favourites.id,
IF (
strcmp( my_favourites.type, 'House' ) ,
IF (
strcmp( my_favourites.type, 'Land' ) ,
IF (
strcmp( buildings.imagen, 'noimage.jpg' ) , buildings.imagen, 'noimage_thumb.jpg'
),
IF (
strcmp( lands.imagen, 'noimage.jpg' ) , lands.imagen, 'noimage_thumb.jpg'
)
),
IF (
strcmp( houses.imagen, 'noimage.jpg' ) , houses.imagen, 'noimage_thumb.jpg'
)
)
FROM my_favourites, buildings, houses, lands, cities, states
WHERE my_favourites.email = '[e-mail]' AND
IF (
strcmp( my_favourites.type, 'House' ) ,
IF (
strcmp( my_favourites.type, 'Land' ) , buildings.id = my_favourites.id, lands.id = my_favourites.id
), houses.id = my_favourites.id
) AND
IF (
strcmp( my_favourites.type, 'House' ) ,
IF (
strcmp( my_favourites.type, 'Land' ) , buildings.id_city = cities.id, lands.id_city = cities.id
), houses.id_city = cities.id
) AND cities.id_state = states.id

Thanks in advance 🙂

[bubblenut]

You should really try to avoid flow control within a query unless it cannot be avoided. Look into using LEFT JOIN on some of your tables, you may get a load of columns with null values and it may be a little confusing to set up if you haven't used jeft joins before but you will have a number of advantages.

a)Quicker query times.
b)Easier to optimize query (with EXPLAIN) to make query even quicker!
c)Standard result set. Unavailable values will be returned just with null values.
HTH
Bubble