Temporary Table and Left Joins not showing results as expected

The reason why you lose empty categories, is most likely (I Mulberry Alexa Bags say this because I don't fully know your db structure) because of the where condition filtering out Mulberry Shoulder Bags all results based on the data in the joined tables.

If for a category all items get filtered out (possibly including the NULL joined values), you will not get this category back from that query anymore. Also the GROUP BY is done on a joined column, that might also effectively wipe out your other categories.

As for the second question, you already state it's taking long; so it's probably not the way to go if you want things to work fast ;) (okay, obvious answer, low hanging fruit, etc). What you might want to do, is get a collection of keys from the filterable categories first, and use that data to select items.

This prevents that you have to join up your entire products table Mulberry Outlet in a temp table (at least, that's what I think you're doing), which of course will take long with the given number of entries. Selecting a list of matching IDs from the given attributes also gives you the advance of using your indexes (more), which a temp-table probably won't have. If this is possible and feasible mainly depends on your schema's structure; but I hope it might lead you to the direction you want to go :)

My experience with joining big tables (even with constraining where is that is takes way too long with MySQL. you might have the same problem. The way I work with said large table (in my case, 5.5mil records) is to gather selection criteria first (in the form of ids present in that large table) and then use that to select useful rows from the large table by using the indices. This might speed things up for you too. As for the category not showing up; changing your GROUP BY to the temp table field might fix it (as that field is always present with the joins). Yhn Mar 2 at 8:08