en_dmitriid

Shame on me. I had a mistake in my previous pos. Thanks to xaprb for pointing this out.

I misplaced quote marks in my second query.

Instead of

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

I wrote this:

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

Note the last quote mark after accomodation=1

:)

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...

Web programmers are generally viewed as "less-than-programmers". Some even think that they are not programmers at all, come to think of it. Indeed, some think that creating something like Codeproject is a piece of cake. Those same people also think that creating a yet another accounting program in Visual Basic (or, god forbid, VBA) is uber-cool and is worthy of all praise.

Now, why would anybody think that? Huh?

Let's consider the so-called "application programmer". Or even the so-called "systems programmer". All they ever need to know is their favorite programming language (C/C++/Java/Object Pascal...). Well, quite often something like a passing knowledge of SQL comes along and a declarative language to store application settings in (usually the INI file format or XML). In toto - at most three technologies: programming language, SQL, XML, and chances are the programmer will use datasets and MSXML to work with the latter to.

What about the web programmer, then?

• Favorite programming language - one of PHP/Ruby/Perl/Java/C#/Python


• SQL


• HTML


• CSS


• JavaScript (sometimes with an added bonus of JSON)


• and quite often XML

Also throw in the bag the fact that web programming is originally and immediately a client-server programming. Most of application programmers don't even have a clue about what's that all about.

So, what we have in the end is that the despised web programmers actually know and do the following:

• They program client-server applications easily and effortlessly


• They combine imperative and declarative programming seamlessly


• They have a more-than-passing knowledge about about non-compatible and non-intersecting technologies and know how to combine them

And as soon as the project starts to grow... We have data replication, distributed servers and son on and so forth...

And still web programming is perceived to be a child's play whereas grown-ups are off working on their "serious" technologies which are supposedly to hard for a web-programmer to comprehend. Well, think again, lads. It is we, web porgrammers, who look down on you because most of the technologies most of you are just beginning to toy with have already been seen, learned and taken to use by us.

BTW, this discrimination against web programming is exactly the reason why web programmers are so behind on tools and libraries. It is only recently that decent tools have sprung up all over the place (such as Ruby on Rails, Django, Seam, Prado and others)

Now that's a bloody shame if you ask me.