can this hideous query be optimized? 1

[theotrain]

im using amfphp to connect flash to a database. im having speed issues with the queries, which i assumed was due mostly to the overhead of using amfphp. but i just tried executing my queries and timing them using php only... they are kinda slow but not bad, except one query sometimes hangs. the page just stops executing, it gives up (says "done" in the browser) without returning the results or giving an error. this happens in flash sometimes as well, so even in flash its receiving no error returned from the function call. it just stops and considers itself done.

i know just enough SQL to get by, and the query that sometimes chokes is kinda terrible, but when i finally got it to work i was so amazed i just left it. is there a better way to do it? any theories on what would cause php to sort of time out while doing it, and stop without creating an error? it seems to choke mostly the first time you try it after a break. subsequent tries usually go through.

SELECT DISTINCT page_items.name, page_items.url, page_items.html_text, (SELECT user_images.cleared FROM user_images WHERE page_items.url = user_images.image_url) as cleared, page_items.text_cleared, (SELECT pages.shortcut_title FROM pages WHERE pages.id = {$page_id}) as shortcut FROM page_items, user_images, pages WHERE page_items.page_id = {$page_id}")

$page_id is a number passed to the function. im basically trying to get the info for every Page Item but it resides in several tables... this monstrosity usually works, except when it doesnt (in my tests $page_id is always the same). on my local server it takes between .004 and .01 seconds and always works. on the remote server it takes between .03 and forever.

any clues? thanks!

 

[theotrain]

let me just just clarify some things. fist, the query formatted better:

SELECT DISTINCT page_items.name, page_items.url, page_items.html_text, (

SELECT user_images.cleared
FROM user_images
WHERE page_items.url = user_images.image_url
) AS cleared, page_items.text_cleared, (

SELECT pages.shortcut_title
FROM pages
WHERE pages.id = {$page_id}
) AS shortcut

FROM page_items, user_images, pages
WHERE page_items.page_id = {$page_id}

i dont think the details of the query are that relevant, just tell me if the structure is stupid, or better done differently.

the other thing is that the first time i access the page that does my queries, i tend to get crappy performance. subsequent tries its pretty fast, and never fails completely. so theres something sinister about that first connection.

i read somewhere that skip-name-resolve can speed up first connections, but i dont know if it can be done on my host. im just using phpMyAdmin. i dont know if most hosts allow you to set things like that anyway.

suggestions?

 

[r937]

your outer query contains a three-table cartesian product

try this instead --

SELECT page_items.name
     , page_items.url
     , page_items.html_text
     , user_images.cleared
     , page_items.text_cleared
     , pages.shortcut_title
  FROM pages
INNER
  JOIN page_items
    ON page_items.page_id = pages.id
INNER
  JOIN user_images
    ON user_images.image_url = page_items.url
 WHERE pages.id = {$page_id}

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[theotrain]

wow! thanks!

im trying to figure out how your example works, since i dont use inner joins normally its hard for me. but whats happening, it seems, is the new query only returns items that have an entry in the user_images table. looking at this part of the original query:

(SELECT user_images.cleared FROM user_images WHERE page_items.url = user_images.image_url) as cleared

it returned whether or not the item was "cleared" but would still return the item even if it wasnt. so my old query returns 30 items in my test case, and the new format returns 2, because im only getting the ones that have a match between page_items.url and user_images.image_url.

i need to get all the returns. i need to know if there are mismatches (hence the AS CLEARED part) yet still to include results that are not cleared. disgracefully, i dont understand your syntax enough to tinker with it.

 

[r937]

change the INNER JOINs to LEFT OUTER JOINs



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[theotrain]

that works perfectly, sort of! i got all the results i want, but with one difference. my first query returned NO results when there were no cases of page_items.page_id = {$page_id}. with the LEFT OUTER JOIN query i always seem to get one return when i shouldnt get any. the one return is just a row with NULL in all the fields.

i added "AND page_items.page_id = {$page_id}" to the end of the thing and it seems to work now.

i think your signature is right. i need to buy your book.

 

[r937]

okay, change the first join back to INNER



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon