Mysql Php，查询很慢

Question

我有一个托管在 sandbox.promls.net 上的应用程序，

我用于检索服务器信息的查询存在一些问题，我仍处于开发阶段，因此没有太多数据加载到数据库中。

这是我正在执行的查询（它是一个视图）：

select SQL_CALC_FOUND_ROWS id , name, contact, email_contact, phone_contact, address, phone, fax, email, website, creation_date, last_modification, zipcode, longitude, latitude, gmtoffset, dstoffset, area_id, area, status , logo, type, owner_id, users, created_by, created_by_id 
    from companies_listing 
    limit 0,15

执行需要 19.6522991657 秒。请帮助我！

视图的结构如下：

视图的结构如下：

 DROP VIEW IF EXISTS `companies_listing`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
  SQL SECURITY DEFINER VIEW `companies_listing` AS
  select `c`.`id` AS `id`, `c`.`name` AS `name`,`c`.`contact` AS `contact`,
    `c`.`phone_contact` AS `phone_contact`,`c`.`email_contact` AS `email_contact`,
    `c`.`address` AS `address`,`c`.`phone` AS `phone`,`c`.`fax` AS `fax`,
    `c`.`owner_id` AS `owner_id`,`c`.`email` AS `email`,
    `c`.`website` AS `website`,`c`.`creation_date` AS `creation_date`,
    `c`.`last_modification` AS `last_modification`,`c`.`zipcode` AS `zipcode`,
    `c`.`type` AS `type`,`c`.`status` AS `status`,`a`.`description` AS `area`,
    `c`.`area_id` AS `area_id`,`c`.`logo` AS `logo`,
    `c`.`created_by` AS `creator_id`,`u`.`fullname` AS `creator`,
    (select count(0) AS `count(*)` from `users` `uu` 
       where (`uu`.`company_id` = `c`.`id`)
    ) AS `users` 
    from (
       (`company` `c` 
       join `areas` `a` 
        on((`a`.`id` = `c`.`area_id`))
       )
       join `users` `u` on((`u`.`id` = `c`.`created_by`))
    );

Query explain select id , name, contact, email_contact, phone_contact, address,
                phone, fax, email, website, creation_date, last_modification, 
                area_id, area, status , logo, type, owner_id, users, creator,
                creator_id
                from companies_listing, Thu Feb 10 17:45:37 2011

id   select_type             table       type     possible_keys  key    key_len ref rows    Extra
1   PRIMARY                 <derived2>  ALL       10             (null)
2   DERIVED                 c           ALL       FK_company_1_company              18  (null)
2   DERIVED                 u           eq_ref      PRIMARY PRIMARY 4   inmobili.c.created_by   1   (null)
2   DERIVED                 a           eq_ref      PRIMARY PRIMARY 4   inmobili.c.area_id  1   (null)
3   DEPENDENT SUBQUERY      uu          ref fk_user_company fk_user_company 4   inmobili.c.id   1   Using index

Answer 1

我发现使用 SQL_CALC_FOUND_ROWS 非常慢..并且在没有限制的情况下获取和复制查询几乎比使用 mysql_num_rows 生成存在的行数要快。

让我知道这是否有帮助

Answer 2

• FROM 子句中的括号不会增加易读性，而且会影响效率（因为主查询在不必要时会变成“派生”），
• 您应该在所有列上建立索引您正在加入/查询的 (`c`.`area_id` 和 `c`.`created_by` 是显而易见的