I thought this stored procedure would be faster than plain selects

Because you're doing a SELECT on a table, even it is obvious that no data would actually be selected (as nothing will satisfy 1 = 0), this likely results in a rowscan of the entire table.

And of course, if your PHP solution doesn't involve a temporary table, that's another thing that adds overhead.

At the moment the table only has 150 rows or so. I'm thinking the main issue is the temp table. Do you think there is a MySQL solution that is just as fast as my PHP solution (which is just a recursive function looking for parents) ??

And, you're probably wondering why I'd be doing this for 150 categories. I was told that the category count may increase to ~600K.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3