The best way to show a lot of items under their sorted categories PHP/MySQL/Performance...

I’m developing a list of books WordPress plugin (PHP/MYSQL).

I have a Book and a Category table like this:

Book table:

• id
• name
• primary_category (Foreign Key to Category.id)

Category table:

• id
• name
• priority (int)

I want to show each book under its primary category's title and the categories should be sort by their priority.

For example:

— category with priority 1

——— book with the primary category 1

——— book with the primary category 1

— category with priority 2

——— book with the primary category 2

...

I know I can select * from category order by priority and then write a foreach loop on the categories and show books with the same category id but as I have a lot of books (>1000) and (>50) categories, foreach loop takes so long and it's not the ideal way.

Could somebody explain to me how can I show all the books under their categories that sorted by priority with the best performance, please?

Continue reading...