en_dmitriid


Tigers, and lions, and bears, oh my!


Previous Entry Share Next Entry
Those evening queries...
happy
dmitriid wrote in en_dmitriid
I'm dealing with databases right now while keeping an eye on «High performance MySQL». Finally decided to try something out. Here we go...

We have a table split into partitions be week:


CREATE TABLE `price_detail` (
`accomodation` int(11) NOT NULL,
`date` datetime NOT NULL,
`price` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
KEY `idx_price_date` (`date`) USING BTREE,
KEY `idx_price` (`price`) USING BTREE,
KEY `idx_price_accomodation` (`accomodation`) USING BTREE
)
PARTITION BY RANGE (TO_DAYS(`date`)) (
PARTITION p20080101 VALUES LESS THAN (733407),
PARTITION p20080108 VALUES LESS THAN (733414),
/* and so on — an entire years worth */


Yep... We have indexes as well...

We have 26 million rows per week in the worst case. Needless to say that we have the worst case for testing purposes .

Let's see how a simple query will work on such a database:

Query I:

explain partitions 
  select 
    accomodation 
  from 
    price_detail 
  where 
    accomodation=1 and date BETWEEN '2008-01-01 12:00:00.000' AND '2008-01-14 12:00:00.000'\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: price_detail
   partitions: p20080108,p20080115
         type: ref
possible_keys: idx_price_date,idx_price_accomodation
          key: idx_price_accomodation
      key_len: 4
          ref: const
         rows: 40292
        Extra: Using where


Query II:


explain partitions 
  select 
    accomodation 
  from 
    price_detail 
  where 
    date BETWEEN '2008-01-01 12:00:00.000' AND '2008-01-14 12:00:00.000 AND accomodation=1'\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: price_detail
   partitions: p20080108,p20080115,p20080122,p20080129,p20080205,p20080212,p20080219,
                     p20080226,p20080304,p20080311,p20080318,p20080325,p20080401,p20080408,
                     p20080415,p20080422,p20080429,p20080506,p20080513,p20080520,p20080527,
                     p20080603,p20080610,p20080617,p20080624,p20080701,p20080708,p20080715,
                     p20080722,p20080729,p20080805,p20080812,p20080819,p20080826,p20080902,
                     p20080909,p20080916,p20080923,p20080930,p20081007,p20081014,p20081021,
                     p20081028,p20081104,p20081111,p20081118,p20081125,p20081202,p20081209,
                     p20081216,p20081223,p20081230,p20090106,p20090113,p20090120,p20090127
         type: ALL
possible_keys: idx_price_date
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 274845000
        Extra: Using where


What a nasty surprise!

I'm off learning about covering indices...
Tags: ,

  • 1
Am I wrong, or is the ONLY difference the order that you typed the query? It still has all the same WHERE clauses, etc but in a different order?

If so, then that is certainly a bug and you should report it.

Wait, I think I see the problem.

'2008-01-14 12:00:00.000 AND accomodation=1'

That is a single value in the second query, because you have enclosed it all in quotes. You need to properly quote the datetime value!

Indeed! Shame on me! Thank you!!!!

:)))

  • 1
?

Log in