MySQL Available Cars With multiple owners

MySQL

I have a complex SQL question. We are building a car rental system. We have a car model table, a owner table and a link_model_owner table. In addition our bookings are kept in another table.

Scheme looks like this:

enter image description here

Now I need a query to search all available cars of a certain model. I've tried the following Query. However, this query will give multiple results if a linkId is added to the booking database and startDate and endDate are empty there.

I just want to have a list of unique available linkId's where the car is not rented out (NOW () between startDate and endDate).

Bear in mind that the booking database will expect to grow with 1000's a day.

SELECT link_model_owner.id,
        owners.fullName
FROM link_model_owner
LEFT JOIN bookings ON link_book_user.id = bookings.bookLinkId
INNER JOIN owners ON link_book_user.userId = owners.id
WHERE link_model_owner.isActive = '1'
  AND link_model_owner.modelid = '252'
  AND COALESCE(NOW() NOT BETWEEN bookings.startDate AND bookings.endDate, TRUE);

Best Answer

It wasn't completely obvious what you wanted to use all tables for, especially link_book_user, but I focused on your main question where you wanted to get all cars that were not currently being booked, taking into consideration that startTime and endTime can be null.

In the query suggestion below I filter bookings on the date already when they're being linked in the join and then the query becomes a bit easier to construct. If a booking has null values as dates the car will be seen as free.

SELECT link_model_owner.id, 
        owners.fullName
FROM link_model_owner
LEFT JOIN bookings ON (link_model_owner.id = bookings.linkId 
                        AND bookings.startDate <= now() 
                        AND bookings.endDate >= now())
INNER JOIN owners ON owners.id = link_model_owner.ownerId
WHERE bookings.id IS null;

Note: It's important to have indexes on startDate and endDate since your database will grow quite some (as you wrote).

TECHNIQUE #1 : Write Stored Procedures to Traverse Recursively

Rather than Reinventing the Wheel, please see my past posts on how to make stored procedures

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Oct 26, 2012 : Hierarchical queries without CTEs

TECHNIQUE #2 : Iterate Based on the Tree's Maximum Depth

First, here is some sample data

DROP DATABASE IF EXISTS dufran;
CREATE DATABASE dufran;
USE dufran;
CREATE TABLE reference 
(
    user INT NOT NULL AUTO_INCREMENT,
    referee INT NOT NULL DEFAULT 0,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY (user)
);
INSERT INTO reference (name,referee) VALUES ('Rolando',0);
INSERT INTO reference (name,referee) VALUES ('Pamela',1);
INSERT INTO reference (name,referee) VALUES ('Carlik',2);
INSERT INTO reference (name,referee) VALUES ('Javonne',2);
INSERT INTO reference (name,referee) VALUES ('Dominique',2);
INSERT INTO reference (name,referee) VALUES ('Diamond',2);
INSERT INTO reference (name,referee) VALUES ('Azalia',3);

When loaded, this is the data

mysql> SELECT * FROM reference;
+------+---------+-----------+
| user | referee | name      |
+------+---------+-----------+
|    1 |       0 | Rolando   |
|    2 |       1 | Pamela    |
|    3 |       2 | Carlik    |
|    4 |       2 | Javonne   |
|    5 |       2 | Dominique |
|    6 |       2 | Diamond   |
|    7 |       3 | Azalia    |
+------+---------+-----------+
7 rows in set (0.00 sec)

mysql>

Here is how you compute the maximum depth

SET @depth = 0;
SELECT MAX(@depth:=@depth+1) max_depth FROM
(SELECT DISTINCT referee FROM reference) A;

Here is the maximum depth for the sample data

mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(@depth:=@depth+1) max_depth FROM
    -> (SELECT DISTINCT referee FROM reference) A;
+-----------+
| max_depth |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

mysql>

Using this principle, iterate for the SELECT columns and the LEFT JOIN clauses

SET @depth = 0;
SELECT GROUP_CONCAT(CONCAT('t',dep,'.user AS lvl',dep))
INTO @sqllist FROM
(SELECT @depth:=@depth+1 dep FROM
(SELECT DISTINCT referee FROM reference) AA) A;
SET @depth1 = 0;
SET @depth2 = 1;
SELECT GROUP_CONCAT(sqljoin SEPARATOR ' ') INTO @sqljoins
FROM (SELECT CONCAT('LEFT JOIN reference t',@depth2:=@depth2+1,
' ON t',@depth2,'.referee = t',@depth1:=@depth1+1,'.user') sqljoin
FROM (SELECT DISTINCT referee FROM reference) AA) A;
SELECT @sqljoins;
SET @sqlstmt = CONCAT('SELECT ',@sqllist,
' FROM reference t1 ',@sqljoins);
SELECT @sqllist\G
SELECT @sqljoins\G
SELECT @sqlstmt\G

Here is the SQL generated

mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('t',dep,'.user AS lvl',dep))
    -> INTO @sqllist FROM
    -> (SELECT @depth:=@depth+1 dep FROM
    -> (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

mysql> SET @depth1 = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @depth2 = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(sqljoin SEPARATOR ' ') INTO @sqljoins
    -> FROM (SELECT CONCAT('LEFT JOIN reference t',@depth2:=@depth2+1,
    -> ' ON t',@depth2,'.referee = t',@depth1:=@depth1+1,'.user') sqljoin
    -> FROM (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

mysql>     SELECT @sqllist\G
*************************** 1. row ***************************
@sqllist: t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4
1 row in set (0.00 sec)

mysql>     SELECT @sqljoins\G
*************************** 1. row ***************************
@sqljoins: LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user
1 row in set (0.00 sec)

mysql>     SELECT @sqlstmt\G
*************************** 1. row ***************************
@sqlstmt: SELECT t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4 FROM reference t1 LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user
1 row in set (0.00 sec)

mysql>

Here is the big question, does the SQL work ??? Take the SQL and run it dynamically:

PREPARE s FROM @sqlstmt;
EXECUTE s;
DEALLOCATE PREPARE s;

Here is the result:

mysql> PREPARE s FROM @sqlstmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s;
+------+------+------+------+
| lvl1 | lvl2 | lvl3 | lvl4 |
+------+------+------+------+
|    1 |    2 |    3 |    7 |
|    1 |    2 |    4 | NULL |
|    1 |    2 |    5 | NULL |
|    1 |    2 |    6 | NULL |
|    2 |    3 |    7 | NULL |
|    2 |    4 | NULL | NULL |
|    2 |    5 | NULL | NULL |
|    2 |    6 | NULL | NULL |
|    3 |    7 | NULL | NULL |
|    4 | NULL | NULL | NULL |
|    5 | NULL | NULL | NULL |
|    6 | NULL | NULL | NULL |
|    7 | NULL | NULL | NULL |
+------+------+------+------+
13 rows in set (0.03 sec)

mysql> DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)

mysql>

I scripted this to give you a starting point and some homework. Here is what I mean: You probably don't want to see NULL columns. You could do one of two things:

Convert each NULL to IFNULL(lvl1,''), GROUP_CONCAT all columns.
Compute the depth of a particular id and gather columns for it only.

Give it a Try !!!

UPDATE 2016-06-14 16:36 EDT

Someone just commented

Your max depth calculation is wrong. Proof: Add to your sample data: INSERT INTO reference (name,referee) VALUES ('Maria',4);

Let's try it out

mysql> INSERT INTO reference (name,referee) VALUES ('Maria',4);
Query OK, 1 row affected (0.00 sec)

mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(@depth:=@depth+1) max_depth FROM
    -> (SELECT DISTINCT referee FROM reference) A;
+-----------+
| max_depth |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

OK, it says the depth is 5. Is there visible proof of this ?

mysql> SET @depth = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('t',dep,'.user AS lvl',dep))
    -> INTO @sqllist FROM
    -> (SELECT @depth:=@depth+1 dep FROM
    -> (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

mysql> SET @depth1 = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @depth2 = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(sqljoin SEPARATOR ' ') INTO @sqljoins
    -> FROM (SELECT CONCAT('LEFT JOIN reference t',@depth2:=@depth2+1,
    -> ' ON t',@depth2,'.referee = t',@depth1:=@depth1+1,'.user') sqljoin
    -> FROM (SELECT DISTINCT referee FROM reference) AA) A;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @sqljoins;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sqljoins                                                                                                                                                                                                                                  |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user LEFT JOIN reference t6 ON t6.referee = t5.user |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @sqlstmt = CONCAT('SELECT ',@sqllist,
    -> ' FROM reference t1 ',@sqljoins);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @sqllist\G
*************************** 1. row ***************************
@sqllist: t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4,t5.user AS lvl5
1 row in set (0.00 sec)

mysql> SELECT @sqljoins\G
*************************** 1. row ***************************
@sqljoins: LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user LEFT JOIN reference t6 ON t6.referee = t5.user
1 row in set (0.00 sec)

mysql> SELECT @sqlstmt\G
*************************** 1. row ***************************
@sqlstmt: SELECT t1.user AS lvl1,t2.user AS lvl2,t3.user AS lvl3,t4.user AS lvl4,t5.user AS lvl5 FROM reference t1 LEFT JOIN reference t2 ON t2.referee = t1.user LEFT JOIN reference t3 ON t3.referee = t2.user LEFT JOIN reference t4 ON t4.referee = t3.user LEFT JOIN reference t5 ON t5.referee = t4.user LEFT JOIN reference t6 ON t6.referee = t5.user
1 row in set (0.00 sec)

mysql> PREPARE s FROM @sqlstmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Guess what the output says ?

mysql> EXECUTE s;
+------+------+------+------+------+
| lvl1 | lvl2 | lvl3 | lvl4 | lvl5 |
+------+------+------+------+------+
|    1 |    2 |    3 |    7 | NULL |
|    1 |    2 |    4 |    8 | NULL |
|    1 |    2 |    5 | NULL | NULL |
|    1 |    2 |    6 | NULL | NULL |
|    2 |    3 |    7 | NULL | NULL |
|    2 |    4 |    8 | NULL | NULL |
|    2 |    5 | NULL | NULL | NULL |
|    2 |    6 | NULL | NULL | NULL |
|    3 |    7 | NULL | NULL | NULL |
|    4 |    8 | NULL | NULL | NULL |
|    5 | NULL | NULL | NULL | NULL |
|    6 | NULL | NULL | NULL | NULL |
|    7 | NULL | NULL | NULL | NULL |
|    8 | NULL | NULL | NULL | NULL |
+------+------+------+------+------+
14 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)

mysql>

Just as my code predicted. The depth is 5 and the Dynamic SQL just proved it.

MySQL Locks During Query with Pending Updates – Invision Power Board 3.4.6

A quick look shows 2 main problems:

Many queries on the stage Waiting for table level lock indicates a concurrency problem due to the usage of a table-level-only lock engine, such as MyISAM. Moving, as you did, to InnoDB will mostly solve the problem, specially for the queries you are doing (counters):
```
UPDATE topics SET views=views+2 WHERE tid=64896
```
Before, only one topic could be updated at the same time. With Innodb, different topics can be updated at the same time. However, you can still suffer from locks due to updates to the same row (same tid), and they will not be show as Waiting for table level lock but as stalls in the state Updating.
You still have probably a high number of temporary tables, and setting your config to:
```
tmp-table-size                  =       384M
max-heap-table-size             =       256M
```
may have the opposite effect, making the queries slower, not faster, if the temporary tables are larger than that. You query:
```
SELECT p.pid, p.post_date, p.post, p.author_id,t.tid, t.title_seo, t.title 
FROM posts p 
LEFT JOIN topics t 
ON ( t.tid=p.topic_id ) 
WHERE t.approved=1 AND 
      t.forum_id IN (lots, of, in, values) AND 
      queued=0 
ORDER BY p.post_date DESC 
LIMIT 0,5
```
is not that complex to require a temporary table -I may be wrong, that is why cannot be said reliably without looking at your table structure and indexes- optimizing its execution by adding the appropriate indexes may solve your main problems. In order to do that, a full check of all your queries and data structure should be performed.

Best Answer

Related Solutions

Mysql – Loop through Self JOIN on table until the operand column is NULL completely

TECHNIQUE #1 : Write Stored Procedures to Traverse Recursively

TECHNIQUE #2 : Iterate Based on the Tree's Maximum Depth

UPDATE 2016-06-14 16:36 EDT

MySQL Locks During Query with Pending Updates – Invision Power Board 3.4.6

Related Question