行使MySQL(和PHP)搜刮可用性吗？

我有两个MySQL(MyIsAm)表,别离代表出租单元和预订：

> LettingUnits(ID,名称等)
> LettingUnitBookings(ID,F_LU_ID,开始,竣事)

个中F_LU_ID是该单位的外键.

搜刮特按时段内可用单元的最佳要领是什么？搜刮通过开始,竣事和一连时刻.

>开始=最早开始预定
>竣事=预订的最后竣事
>一连时刻=预订一连时刻

我想知道是否有也许在MySQL中执行此操纵,可是假如不能,那么最好的要领是在PHP中执行此操纵.

例

在答复下面的谜底时,我认为一个例子将有助于表明题目.

一个LettingUnit：

>(123,“ Foo Cottage”)

一些LettingUnitBookings：

>(400,123,01/01/09,05/01/09)-5天预订
>(401,10/01/09,20/01/09)-10天预订
>(402,25/01/09,30/01/09)-5天预订

假如我们搜刮：

>开始= 01/01/09
>竣事= 01/02/2009
>一连时刻= 5(天)

然后,我们要表现该单位.由于在搜刮范畴内可以预订5天.

假如一连时刻为10,则该单位将不会表现,由于在搜刮范畴内没有持续10天的未预订天数.

SELECT t.*,DATEDIFF(t.LatestAvailable,t.EarliestAvailable) AS LengthAvailable
FROM 
   (SELECT u.*,COALESCE(b1.End,@StartOfWindow) AS EarliestAvailable,COALESCE(b2.Start,@EndOfWindow) AS LatestAvailable
    FROM LettingUnits u
    LEFT OUTER JOIN LettingUnitBookings b1
      ON (u.ID = b1.F_LU_ID AND b1.End BETWEEN @StartOfWindow AND @EndOfWindow)
    LEFT OUTER JOIN LettingUnitBookings b2
      ON (u.ID = b2.F_LU_ID AND b2.Start BETWEEN @StartOfWindow AND @EndOfWindow
          AND b2.Start >= b1.End) -- edit: new term
    ) AS t
LEFT OUTER JOIN LettingUnitBookings x
  ON (t.ID = x.F_LU_ID AND x.Start < t.LatestAvailable AND x.End > t.EarliestAvailable)
WHERE x.ID IS NULL AND DATEDIFF(t.LatestAvailable,t.EarliestAvailable) >= @WindowSize;

输出为：

+-----+-------------+-------------------+-----------------+-----------------+
| ID  | Name        | EarliestAvailable | LatestAvailable | LengthAvailable |
+-----+-------------+-------------------+-----------------+-----------------+
| 123 | Foo Cottage | 2009-01-05        | 2009-01-10      |               5 |
| 123 | Foo Cottage | 2009-01-20        | 2009-01-25      |               5 |
| 456 | Bar Cottage | 2009-01-20        | 2009-01-31      |              11 |
+-----+-------------+-------------------+-----------------+-----------------+

用EXPLAIN对此举办说明表白,它很好地操作了索引：

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra                   |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL  |    9 | Using where             |
|  1 | PRIMARY     | x          | ref    | F_LU_ID       | F_LU_ID | 8       | t.ID  |    2 | Using where; Not exists |
|  2 | DERIVED     | u          | system | NULL          | NULL    | NULL    | NULL  |    1 |                         |
|  2 | DERIVED     | b1         | ref    | F_LU_ID       | F_LU_ID | 8       | const |    0 |                         |
|  2 | DERIVED     | b2         | ref    | F_LU_ID       | F_LU_ID | 8       | const |    0 |                         |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+

与@martin clayton的given办理方案的EXPLAIN陈诉举办较量：

+----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+
| id | select_type  | table               | type   | possible_keys | key     | key_len | ref  | rows | Extra                           |
+----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+
|  1 | PRIMARY      | lu                  | system | PRIMARY,ID    | NULL    | NULL    | NULL |    1 |                                 |
|  1 | PRIMARY      | <derived2>          | ALL    | NULL          | NULL    | NULL    | NULL |    4 | Using where                     |
|  2 | DERIVED      | <derived3>          | ALL    | NULL          | NULL    | NULL    | NULL |    4 | Using temporary; Using filesort |
|  2 | DERIVED      | <derived5>          | ALL    | NULL          | NULL    | NULL    | NULL |    4 | Using where; Using join buffer  |
|  5 | DERIVED      | LettingUnitBookings | ALL    | NULL          | NULL    | NULL    | NULL |    3 |                                 |
|  6 | UNION        | LettingUnitBookings | index  | NULL          | F_LU_ID | 8       | NULL |    3 | Using index                     |
| NULL | UNION RESULT | <union5,6>          | ALL    | NULL          | NULL    | NULL    | NULL | NULL |                                 |
|  3 | DERIVED      | LettingUnitBookings | ALL    | NULL          | NULL    | NULL    | NULL |    3 |                                 |
|  4 | UNION        | LettingUnitBookings | index  | NULL          | F_LU_ID | 8       | NULL |    3 | Using index                     |
| NULL | UNION RESULT | <union3,4>          | ALL    | NULL          | NULL    | NULL    | NULL | NULL |                                 |
+----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+

凡是,您要停止逼迫行使文件排序或行使姑且的优化打算,由于这些打算会低落机能.行使GROUP BY的查询险些可以必定会导致这种优化,至少在MySQL中云云.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!