Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date Comparison not using Index

Posted on 2012-03-26
8
Medium Priority
?
288 Views
Last Modified: 2012-08-01
Hi All:

I am trying to do the following SQL statement in MySQL:

SELECT DATE_FORMAT(dates.`datum`, '%Y-%m-%d 12:00:00') AS date2,
	(SELECT COUNT(*) FROM bookings 
			WHERE bookings.to >= date2
			AND bookings.from <= date2) AS number_of_bookings


FROM dates

 WHERE dates.`datum` >= '2012-01-01' 
  AND dates.`datum` <= '2012-01-31' 
AND DATE_FORMAT(dates.`datum`, '%H:%i:%s') = '12:00:00'

Open in new window


I have a table "dates" which has all dates from now until 2030 and each day segmented in 15 minute slots, hence the 12:00:00 filter....

The table bookings has (who would have known) bookings which can go over multiple days.

In essence, I want to know who many units are rented out each day.

The above SQL does work, but (using EXPLAIN) I found it will look through the whole bookings table instead of using the indexes which I have on bookings.to and bookings.from thus limiting it to the daterange.

How can I get MySQL to limit where its looking to the index?

Thanks,

T :-)
0
Comment
Question by:kracklt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 37768704
Can you post the EXPLAIN output?  Also try to compare bookings.to and bookings.from to the "native" dates.datum.  It might be comparing it to the output of date_format that's killing it.  It should at least use one of the indexes (I don't expect it to use both because of the range comparison).
0
 

Author Comment

by:kracklt
ID: 37768803
Hi Johann:

Find the Explain Output enclosed as GIF.

I tried your suggestion to compare with the dates.datum but the explain brought the same solution back.

Any other ideas?

Thomas :-)
EXPLAIN Output
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37768956
Googling about it tells me MySQL is really still deficient when it comes to optimizing subqueries.  I currently cannot think of a way to rewrite the query, so my only suggestion right now is for you to save the results of the query to a table so that you can query that table for future reference vs re-running the full query.  You can have a script that inserts to the table regulary, maybe daily:

insert into report_table (report_date, total_bookings)
    SELECT '2012-03-27 12:00:00', COUNT(*) FROM bookings
                  WHERE bookings.to >= '2012-03-27 12:00:00'
                  AND bookings.from <= '2012-03-27 12:00:00';
0
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 

Author Comment

by:kracklt
ID: 37769186
Hi johann:

Not sure if that will work since the number of units rented out can change any moment. But you gave me an idea. I wonder if I could make a view and use that as a basis. I will try in the morning and report back.

Thanks for now,

Thomas :-)
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 37769318
A view is just a "shortcut" - it will still run the  same query you used to create it every time you query the view.  

Okay I thought you were just querying for past days, but now I realize you also want the check for future dates.  One thing I can think is to use triggers to update the report table for every new row inserted.  Something like:

mysql> create table bookings(id int not null auto_increment primary key, `from` datetime, `to` datetime);
Query OK, 0 rows affected (0.06 sec)

mysql> create table report_table (report_date datetime not null, total_bookings int not null default 0, primary key (report_date));
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter |
mysql> create trigger bookings_insert
    -> after insert on bookings
    -> for each row
    -> begin
    ->   DECLARE row_date datetime;
    ->   set row_date=concat(date(NEW.`from`),' 12:00:00');
    ->   while row_date < NEW.`to` do
    ->     insert into report_table (report_date, total_bookings) values (row_date, 1) on duplicate key update total_bookings = total_bookings + 1;
    ->     set row_date=date_add(row_date, interval 1 day);
    ->   end while;
    -> end; |
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> delimiter ;
mysql> insert into bookings (`from`,`to`) values ('2012-03-26','2012-03-28');
Query OK, 1 row affected (0.00 sec)

mysql> select * from report_table;
+---------------------+----------------+
| report_date         | total_bookings |
+---------------------+----------------+
| 2012-03-26 12:00:00 |              1 |
| 2012-03-27 12:00:00 |              1 |
+---------------------+----------------+
2 rows in set (0.00 sec)

mysql> insert into bookings (`from`,`to`) values ('2012-03-26','2012-03-27');
Query OK, 1 row affected (0.00 sec)

mysql> select * from report_table;
+---------------------+----------------+
| report_date         | total_bookings |
+---------------------+----------------+
| 2012-03-26 12:00:00 |              2 |
| 2012-03-27 12:00:00 |              1 |
+---------------------+----------------+
2 rows in set (0.00 sec)

mysql> insert into bookings (`from`,`to`) values ('2012-03-27','2012-03-28');
Query OK, 1 row affected (0.00 sec)

mysql> select * from report_table;
+---------------------+----------------+
| report_date         | total_bookings |
+---------------------+----------------+
| 2012-03-26 12:00:00 |              2 |
| 2012-03-27 12:00:00 |              2 |
+---------------------+----------------+
2 rows in set (0.00 sec)

mysql> insert into bookings (`from`,`to`) values ('2012-03-27','2012-03-29');
Query OK, 1 row affected (0.00 sec)

mysql> select * from report_table;
+---------------------+----------------+
| report_date         | total_bookings |
+---------------------+----------------+
| 2012-03-26 12:00:00 |              2 |
| 2012-03-27 12:00:00 |              3 |
| 2012-03-28 12:00:00 |              1 |
+---------------------+----------------+
3 rows in set (0.00 sec)

Open in new window


You can also create a delete/update trigger that decrement the rows in report_table when there's a booking deletion/cancellation.

Of course with this you will need to pre-populate the report_table from the previous records.  Or, if they are not a lot, you can reinsert columns to the bookings table:

mysql> truncate table report_table;
Query OK, 0 rows affected (0.00 sec)

mysql> create table bookings_temp as select * from bookings;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> truncate table bookings;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bookings select * from bookings_temp;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from report_table;
+---------------------+----------------+
| report_date         | total_bookings |
+---------------------+----------------+
| 2012-03-26 12:00:00 |              2 |
| 2012-03-27 12:00:00 |              3 |
| 2012-03-28 12:00:00 |              1 |
+---------------------+----------------+
3 rows in set (0.00 sec)

Open in new window

0
 

Author Comment

by:kracklt
ID: 37777363
Hi John:

Sorry for the late reply. I was out of the office unexpectedly yesterday.

Trigger may be a good idea....as you said, I would have to prefill the table (there are 39.000 Entries so far...).....

Too bad. I was hoping I could do this with a simple query :-(

Thank you so mcuh for your help! I will try to go down the trigger way :-)

Thomas :-)
0
 

Author Closing Comment

by:kracklt
ID: 37777370
Unfortunately its not possible to force MySQL to use the index keys, but the provided alternative solution is a great idea :-)
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37780296
Glad to help =)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question