Categories and Subcategories problem with MySQL?

[eluser]dojolab[/eluser]
[quote author="Twisted1919" date="1276297266"]I bet my recursive function is way too fast than your joined + grouped + distinct query .
Go recursive, it's not about you like it or not, it's just it's easier and faster .
If you want to get more levels you need to join tables once again for each join, joins in mysql are expensive.
Believe me, i have had fight allot with this to get my opinion about it, it's recursive, but it's fast .[/quote]

Really? Sorry, can you post it a query sample for your recursive function?

[eluser]Twisted1919[/eluser]
I just did, i posted my function above , an example of produced result is this :

Code:

0.0003      SELECT category_id,name,image,url FROM categories  WHERE parent_id IS NULL ORDER BY sort_order ASC  
0.0002      SELECT category_id,name FROM categories WHERE parent_id IS NULL AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories  WHERE parent_id = '8' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories  WHERE parent_id = '10' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '11' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '12' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '13' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '14' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '15' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '16' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '17' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '18' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '19' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '20' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '21' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '22' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '23' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '24' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '25' AND  sort_order > -1 ORDER BY sort_order ASC  
0.0001      SELECT category_id,name FROM categories WHERE parent_id = '26' AND  sort_order > -1 ORDER BY sort_order ASC

Maybe i didn't understand the question ?

[eluser]Twisted1919[/eluser]
Ah, not to forget, i am caching the result so the query happens only once i add/edit a category/subcategory

[eluser]dojolab[/eluser]
[quote author="Twisted1919" date="1276298248"]Ah, not to forget, i am caching the result so the query happens only once i add/edit a category/subcategory[/quote]

Thanks, is a good solution