So how would you optimize such a statement? AFAICT you could get rid of one JOIN because s_user_attributes is unused. But I still think any database should be able to do this and the rest quickly given the right indexes. I assume that s_user.id is a primary key which should make the ORDER BY and LIMIT easy.
And how would you implement paging over a result set over distinct HTTP requests? You could argue that this functionality does not make sense in the first place if you are talking about more than a few hundred users. But requirements are, unfortunately, often not in a developer's power to decide. Also, if you are customizing off-the-shelf software, you typically do not have (much) control over the schema.
Again with slightly better formatting:
SELECT DISTINCT s0_.id AS id_0, s1_.name AS name_1, s2_.description AS description_2, s3_.countryname AS countryname_3, s0_.id AS id_4
FROM s_user s0_
LEFT JOIN s_core_shops s1_ ON s0_.subshopID = s1_.id
LEFT JOIN s_user_addresses s4_ ON s0_.default_billing_address_id = s4_.id
LEFT JOIN s_core_countries s3_ ON s4_.country_id = s3_.id
LEFT JOIN s_user_attributes s5_ ON s0_.id = s5_.userID
LEFT JOIN s_core_customergroups s2_ ON s0_.customergroup = s2_.groupkey
ORDER BY s0_.id DESC LIMIT 20 OFFSET 0
I think you could argue that you should not be doing what the developer is doing in the first place. But I fail to see how the statement itself could be done any better except for the one superfluous JOIN. Care to enlighten me?
(And yes, I work in software development and know this very situation quite well. I help run an e-commerce site with tens of million registered users and a few tables with 10e8 rows. I have my fair share of "It was fast in my VM" stories, but this does not look like one to me or at least that is not the whole story.)<\/div><\/div>
<\/div>","width":640,"height":426}
And how would you implement paging over a result set over distinct HTTP requests? You could argue that this functionality does not make sense in the first place if you are talking about more than a few hundred users. But requirements are, unfortunately, often not in a developer's power to decide. Also, if you are customizing off-the-shelf software, you typically do not have (much) control over the schema.
Again with slightly better formatting:
SELECT DISTINCT s0_.id AS id_0, s1_.name AS name_1, s2_.description AS description_2, s3_.countryname AS countryname_3, s0_.id AS id_4
FROM s_user s0_
LEFT JOIN s_core_shops s1_ ON s0_.subshopID = s1_.id
LEFT JOIN s_user_addresses s4_ ON s0_.default_billing_address_id = s4_.id
LEFT JOIN s_core_countries s3_ ON s4_.country_id = s3_.id
LEFT JOIN s_user_attributes s5_ ON s0_.id = s5_.userID
LEFT JOIN s_core_customergroups s2_ ON s0_.customergroup = s2_.groupkey
ORDER BY s0_.id DESC LIMIT 20 OFFSET 0
I think you could argue that you should not be doing what the developer is doing in the first place. But I fail to see how the statement itself could be done any better except for the one superfluous JOIN. Care to enlighten me?
(And yes, I work in software development and know this very situation quite well. I help run an e-commerce site with tens of million registered users and a few tables with 10e8 rows. I have my fair share of "It was fast in my VM" stories, but this does not look like one to me or at least that is not the whole story.)<\/div><\/div>
<\/div>","width":640,"height":426}