Mysql减法性能低下

发布于 2024-12-18 09:12:30 字数 799 浏览 1 评论 0原文

我在我的应用程序中进行以下查询，该查询检索行：

SELECT 
    title, 
    SUBSTRING(description, 1, 200) AS s_description, 
    refresh_time, (UNIX_TIMESTAMP()-refresh_time) AS since, 
    state, state_friendly, 
    city, city_friendly, 
    category, category_friendly, 
    basename, COUNT(ad_id) AS im_ocur
FROM `ad` `a` 
JOIN `state` ON state_id = ad_state_id 
JOIN `city` ON city_id = ad_city_id 
JOIN `category` ON category_id = ad_category_id 
LEFT JOIN `ad_image` ON ad_image_ad_id = ad_id 
WHERE (ad_country_id = 195) 
GROUP BY `ad_id` 
ORDER BY `refresh_time` DESC, front DESC 
LIMIT 20

我已经在很多情况下对其进行了测试，但是当引用具有大量行的城市时，性能有所下降。经过几次更改后，我意识到问题是由于“(UNIX_TIMESTAMP()-refresh_time)”表达式引起的，所以看来Mysql引擎在行限制设置为20之前进行算术运算。

我的替代解决方案是使用PHP后处理数据，但我更喜欢完整的 mysql 方式。

是否可以？

原文

I am making the following query in my applicattion, that retrieves rows:

SELECT 
    title, 
    SUBSTRING(description, 1, 200) AS s_description, 
    refresh_time, (UNIX_TIMESTAMP()-refresh_time) AS since, 
    state, state_friendly, 
    city, city_friendly, 
    category, category_friendly, 
    basename, COUNT(ad_id) AS im_ocur
FROM `ad` `a` 
JOIN `state` ON state_id = ad_state_id 
JOIN `city` ON city_id = ad_city_id 
JOIN `category` ON category_id = ad_category_id 
LEFT JOIN `ad_image` ON ad_image_ad_id = ad_id 
WHERE (ad_country_id = 195) 
GROUP BY `ad_id` 
ORDER BY `refresh_time` DESC, front DESC 
LIMIT 20

I have tested it at many situations, but when referring to a city with lots of rows, performance it's been decreased. After several changes i realized that the problem was due because of "(UNIX_TIMESTAMP()-refresh_time)" expression, so it seems Mysql Engine make arithmetic operations before the row limitation is set to 20.

My alternative solution is post-processing data with PHP, but i prefer the full mysql way.

Is it possible?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

知你几分 2024-12-25 09:12:30

我认为这不是减法，而是为每一行调用 UNIX_TIMESTAMP() （不过我可能是错的）。

如果您认为减法是性能低下的原因，您可以随时将其移至外部查询。

SELECT
  title, 
  s_description, 
  refresh_time, (UNIX_TIMESTAMP()-refresh_time) AS since,
  state, state_friendly, 
  city, city_friendly, 
  category, category_friendly, 
  basename, im_ocur
FROM (
  SELECT 
    title, 
    SUBSTRING(description, 1, 200) AS s_description, 
    refresh_time,
    state, state_friendly, 
    city, city_friendly, 
    category, category_friendly, 
    basename, COUNT(ad_id) AS im_ocur
  FROM `ad` `a` 
  JOIN `state` ON state_id = ad_state_id 
  JOIN `city` ON city_id = ad_city_id 
  JOIN `category` ON category_id = ad_category_id 
  LEFT JOIN `ad_image` ON ad_image_ad_id = ad_id 
  WHERE (ad_country_id = 195) 
  GROUP BY `ad_id` 
  ORDER BY `refresh_time` DESC, front DESC 
  LIMIT 20
) AS sq

I think it is not a substraction, but UNIX_TIMESTAMP() being called for each row (I might be wrong though).

If you think substraction is the reason of low performance, you can always move it to outer query.

SELECT
  title, 
  s_description, 
  refresh_time, (UNIX_TIMESTAMP()-refresh_time) AS since,
  state, state_friendly, 
  city, city_friendly, 
  category, category_friendly, 
  basename, im_ocur
FROM (
  SELECT 
    title, 
    SUBSTRING(description, 1, 200) AS s_description, 
    refresh_time,
    state, state_friendly, 
    city, city_friendly, 
    category, category_friendly, 
    basename, COUNT(ad_id) AS im_ocur
  FROM `ad` `a` 
  JOIN `state` ON state_id = ad_state_id 
  JOIN `city` ON city_id = ad_city_id 
  JOIN `category` ON category_id = ad_category_id 
  LEFT JOIN `ad_image` ON ad_image_ad_id = ad_id 
  WHERE (ad_country_id = 195) 
  GROUP BY `ad_id` 
  ORDER BY `refresh_time` DESC, front DESC 
  LIMIT 20
) AS sq

回复收藏 0 原文

~没有更多了~