yahoo-eng-team team mailing list archive

[zhen <1994012@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

Image_get_all () uses the left outer join method to query the image_tags
table, and the conversion to the sql statement is as follows:

```
...
AS anon_1 LEFT OUTER JOIN image_properties AS image_properties_1 ON anon_1.id = image_properties_1.image_id LEFT OUTER JOIN image_tags AS image_tags_1 ON anon_1.id = image_tags_1.image_id LEFT OUTER JOIN image_locations AS image_locations_1 ON anon_1.id = image_locations_1.image_id ORDER BY anon_1.created_at DESC, anon_1.id DESC
...
```

Since there is no subquery on image_tags in advance to remove logically
deleted entries, when there are a large number of logically deleted
entries in image_tags (such as frequent update image tag), the query
will be very slow.

Can we first make a query on the image_tags table when joining the left
outer join? For example:

```
...
AS anon_1 LEFT OUTER JOIN image_properties AS image_properties_1 ON anon_1.id = image_properties_1.image_id LEFT OUTER JOIN (SELECT * FROM image_tags where image_tags.deleted = 0) AS image_tags_1 ON anon_1.id = image_tags_1.image_id LEFT OUTER JOIN image_locations AS image_locations_1 ON anon_1.id = image_locations_1.image_id ORDER BY anon_1.created_at DESC, anon_1.id DESC
...
```
How can i express such a sql statement in sqlalchemy?

** Affects: glance
     Importance: Undecided
         Status: New

** Description changed:

  Image_get_all () uses the left outer join method to query the image_tags
  table, and the conversion to the sql statement is as follows:
  
  ```
  ...
  AS anon_1 LEFT OUTER JOIN image_properties AS image_properties_1 ON anon_1.id = image_properties_1.image_id LEFT OUTER JOIN image_tags AS image_tags_1 ON anon_1.id = image_tags_1.image_id LEFT OUTER JOIN image_locations AS image_locations_1 ON anon_1.id = image_locations_1.image_id ORDER BY anon_1.created_at DESC, anon_1.id DESC
  ...
  ```
  
  Since there is no subquery on image_tags in advance to remove logically
  deleted entries, when there are a large number of logically deleted
  entries in image_tags (such as frequent update image tag), the query
  will be very slow.
  
  Can we first make a query on the image_tags table when joining the left
  outer join? For example:
  
  ```
  ...
  AS anon_1 LEFT OUTER JOIN image_properties AS image_properties_1 ON anon_1.id = image_properties_1.image_id LEFT OUTER JOIN (SELECT * FROM image_tags where image_tags.deleted = 0) AS image_tags_1 ON anon_1.id = image_tags_1.image_id LEFT OUTER JOIN image_locations AS image_locations_1 ON anon_1.id = image_locations_1.image_id ORDER BY anon_1.created_at DESC, anon_1.id DESC
  ...
  ```
- How do you express such a sql statement in sqlalchemy?
+ How can i express such a sql statement in sqlalchemy?

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to Glance.
https://bugs.launchpad.net/bugs/1994012

Title:
  list method query all image-related tags that were already deleted

Status in Glance:
  New

Bug description:
  Image_get_all () uses the left outer join method to query the
  image_tags table, and the conversion to the sql statement is as
  follows:

  ```
  ...
  AS anon_1 LEFT OUTER JOIN image_properties AS image_properties_1 ON anon_1.id = image_properties_1.image_id LEFT OUTER JOIN image_tags AS image_tags_1 ON anon_1.id = image_tags_1.image_id LEFT OUTER JOIN image_locations AS image_locations_1 ON anon_1.id = image_locations_1.image_id ORDER BY anon_1.created_at DESC, anon_1.id DESC
  ...
  ```

  Since there is no subquery on image_tags in advance to remove
  logically deleted entries, when there are a large number of logically
  deleted entries in image_tags (such as frequent update image tag), the
  query will be very slow.

  Can we first make a query on the image_tags table when joining the
  left outer join? For example:

  ```
  ...
  AS anon_1 LEFT OUTER JOIN image_properties AS image_properties_1 ON anon_1.id = image_properties_1.image_id LEFT OUTER JOIN (SELECT * FROM image_tags where image_tags.deleted = 0) AS image_tags_1 ON anon_1.id = image_tags_1.image_id LEFT OUTER JOIN image_locations AS image_locations_1 ON anon_1.id = image_locations_1.image_id ORDER BY anon_1.created_at DESC, anon_1.id DESC
  ...
  ```
  How can i express such a sql statement in sqlalchemy?

To manage notifications about this bug go to:
https://bugs.launchpad.net/glance/+bug/1994012/+subscriptions