Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I had a similar question open on SO for postgres - now having the same issue with mysql..

I have two tables -

Table A : 1MM rows, AsOfDate, Id, BId (foreign key to table B)

Table B : 50k rows, Id, Flag, ValidFrom, ValidTo

Table A contains multiple records per day between 2011/01/01 and 2011/12/31 across 100 BId's. Table B contains multiple non overlapping (between validfrom and validto) records for 100 Bids.

The task of the join will be to return the flag that was active for the BId on the given AsOfDate.

select 
    a.AsOfDate, b.Flag 
from 
    A a inner Join B b on 
        a.BId = b.Id and b.ValidFrom <= a.AsOfDate and b.ValidTo >= a.AsOfDate
where
    a.AsOfDate >= 20110101 and a.AsOfDate <= 20111231

This query takes over 3 minutes on a very high end server (+3Ghz) with 64Gb of memory.

+-------+-------------------------+
| Table | Create Table            
|
+-------+-------------------------+
| a     | CREATE TABLE `a` (
  `asofdate` int(4) NOT NULL,
  `bid` int(4) NOT NULL,
  KEY `asofdate_bid` (`asofdate`,`bid`),
  KEY `bid` (`bid`),
  KEY `bid_asofdate` (`bid`,`asofdate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------+

+-------+-------------------------+
| Table | Create Table            |
+-------+-------------------------+
| b     | CREATE TABLE `b` (
  `key` int(4) NOT NULL,
  `id` int(4) NOT NULL,
  `flag` char(1) NOT NULL,
  `validfrom` int(4) NOT NULL,
  `validto` int(4) NOT NULL,
  KEY `id` (`id`),
  KEY `validfrom` (`validfrom`),
  KEY `validfrom_id` (`validfrom`,`id`),
  KEY `id_validfrom` (`id`,`validfrom`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------+

Here is the explain :

mysql> explain select count(1) from a a inner join b b on a.bid = b.id and b.validfrom <= a.asofdate and b.validto >= a.asofdate where a.asofdate >= 20120101 and a.asofdate <= 20121231;

+----+-------------+-------+------+----------------------------------------+--------------+---------+----------+-------+-----------+
| id | select_type | table | type | possible_keys                          | key          | key_len | ref      | rows  | Extra
+----+-------------+-------+------+----------------------------------------+--------------+---------+----------+-------+-----------+
|  1 | SIMPLE      | b     | ALL  | id,validfrom,validfrom_id,id_validfrom | NULL         | NULL    | NULL     | 50510 |                          |
|  1 | SIMPLE      | a     | ref  | asofdate_bid,bid,bid_asofdate          | bid_asofdate | 4       | foo.b.id |  1433 | Using where; Using index |
+----+-------------+-------+------+----------------------------------------+--------------+---------+----------+-------+-----------+

SqlServer express and Postgres take ~300ms to execute the above query. I'm in the process of deciding on a multi-terabyte installation and it's not looking good for mySql (my preferred db) at the moment!

execution plan for the suggested queries

remove the join conditions (3 minutes):

mysql> EXPLAIN SELECT count(1) FROM a a 
    -> INNER JOIN b b ON a.bid = b.id 
    -> WHERE (a.asofdate >= 20120101 and a.asofdate <= 20121231) 
    ->  AND (b.validfrom <= a.asofdate AND b.validto >= a.asofdate);
+----+-------------+-------+------+----------------------------------------+--------------+---------+----------+-------+--------------------------+
| id | select_type | table | type | possible_keys                          | key          | key_len | ref      | rows  | Extra                    |
+----+-------------+-------+------+----------------------------------------+--------------+---------+----------+-------+--------------------------+
|  1 | SIMPLE      | b     | ALL  | id,validfrom,validfrom_id,id_validfrom | NULL         | NULL    | NULL     | 50510 |                          |
|  1 | SIMPLE      | a     | ref  | asofdate_bid,bid,bid_asofdate          | bid_asofdate | 4       | foo.b.id |  1433 | Using where; Using index |
+----+-------------+-------+------+----------------------------------------+--------------+---------+----------+-------+--------------------------+
2 rows in set (0.02 sec)

use straight_join actually changes the query plan and causes the time to go to 6 minutes:

mysql> EXPLAIN SELECT count(1) FROM a a  STRAIGHT_JOIN b b ON a.bid = b.id  WHERE (a.asofdate >= 20120101 and a.asofdate <= 20121231)   AND (b.validfrom <= a.asofdate AND b.validto >= a.asofdate);
+----+-------------+-------+-------+----------------------------------------+--------------+---------+-----------+--------+--------------------------+
| id | select_type | table | type  | possible_keys                          | key          | key_len | ref       | rows   | Extra                    |
+----+-------------+-------+-------+----------------------------------------+--------------+---------+-----------+--------+--------------------------+
|  1 | SIMPLE      | a     | range | asofdate_bid,bid,bid_asofdate          | asofdate_bid | 4       | NULL      | 500296 | Using where; Using index |
|  1 | SIMPLE      | b     | ref   | id,validfrom,validfrom_id,id_validfrom | id           | 4       | foo.a.bid |    255 | Using where              |
+----+-------------+-------+-------+----------------------------------------+--------------+---------+-----------+--------+--------------------------+
share|improve this question
    
did you construct the tables on the SQL Server and postgresql versions of this query? –  swasheck Nov 8 '12 at 16:15
2  
In terms of optimizer alone, MySQL is by far the easiest optimizer to confuse of those three - in my experience. –  rfusca Nov 8 '12 at 16:43
1  
@headsling : I'd try STRAIGHT_JOIN instead of INNER to see if makes a difference. –  a1ex07 Nov 8 '12 at 18:06
    
@a1ex07 see my edit .. took the time to 6 minutes! –  headsling Nov 8 '12 at 20:34

4 Answers 4

My guess is that your join condition is confusing the the MySQL optimizer, and as the explain shows, it's loading the entire b table. What does this give you:

EXPLAIN SELECT count(1) FROM a a 
INNER JOIN b b ON a.bid = b.id 
WHERE (a.asofdate >= 20120101 and a.asofdate <= 20121231) 
 AND (b.validfrom <= a.asofdate AND b.validto >= a.asofdate);

Sidenote, you shouldn't need the KEY (bid) on table A, as KEY bid_asofdate (bid, asofdate) will take care of this, and the way InnoDB handles indexing, this just takes up more space than is needed.

Some further ramblings on indexing. Why don't you define a Primary Key in any of the tables? I would update your table b like this:

CREATE TABLE `b` (
  `key` int(4) NOT NULL PRIMARY KEY,
  `id` int(4) NOT NULL,
  `flag` char(1) NOT NULL,
  `validfrom` int(4) NOT NULL,
  `validto` int(4) NOT NULL,
  KEY `validfrom_id` (`validfrom`,`id`),
  KEY `id_validfrom_validto` (`id`,`validfrom`, `validto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

with the assumption that id is really not the primary key and key is actually useful :)

share|improve this answer
2  
I doubt mysql optimizer can be confused by join in this case( just 2 tables, inner join, no OR); unless there is a serious bug in optimizer, it should generate the same plan regardless of the order and place of conditions... –  a1ex07 Nov 8 '12 at 16:45
1  
... or if it gives up on one of the 5040 possible key combinations. –  swasheck Nov 8 '12 at 16:51
4  
@headsling I always find better to set up tests as close to the real scenario as possible. This is especially true if it comes to performance. –  dezso Nov 9 '12 at 16:51
4  
Totally agree with @dezso. I wouldn't bother spending time on a problem that involves tables without primary keys. For InnoDB, the choice of clustered index (which usually is the Primary Key) is vital for performance and execution plans of many queries. –  ypercube Nov 9 '12 at 17:04
4  
@headsling Instead of plain contradiction, if I were you, I'd try the same with PKs. Only then would you know whether those are irrelevant or not. (If everybody seems to drive on the wrong side of the motorway, what do you suspect?) –  dezso Nov 12 '12 at 17:04

First, try adding another index on table b that includes all columns in the query (id, validfrom, validto) should help.

Second, try adding another criterion in the where clause constraining table b on the date constants. This may help limit the number of rows scanned from b:

AND (b.validfrom <= 20120101 AND b.validto >= 20121231)

Lastly, MySQL doesn't have descending indexing capabilities. If it did you could create an index on id ascending, validfrom ascending, and validto descending which would align better with the query constraints. Such an index would allow for more efficient index scanning in the case.

The common workaround for this is to add a reverse column for the column you wish to index in descending order. See http://stackoverflow.com/questions/1255143/workaround-for-missing-descending-index-feature-in-mysql for an example.

In this case, add a reversevalidto column to table b. Then populate it with -validto. Next create an index on the id, validfrom, and reversevalidfrom columns. Lastly, replace every occurrence of b.validto >= with b.reversevalidto <= in the query.

share|improve this answer
4  
@headsling sooooo ... if postgres is performing that much better, why not just use postgres? –  swasheck Nov 8 '12 at 20:43
1  
@swasheck not sure i'm following? so i should just give up on understanding why the performance is so bad here? –  headsling Nov 9 '12 at 16:22
1  
@headsling well, if you'd like to make it your personal mission in life to understand the subtle nuances between database engines and how their planners optimize the algebra, then by all means tilt at the windmills. it seems, though, that you have an actual business problems that you're trying to solve and there are at least two solutions that have proven better, one of those is FOSS just like MySQL. i guess i'm just not following your rationale either. –  swasheck Nov 9 '12 at 16:34
2  
@headsling as a follow-up, if you'd really like to learn the differences and reasoning, then i'd suggest you spend a bit of time going through this site with each of your RDBMS'. i'd start with dropping ALL implicit key/index/whatever declarations in your CREATE TABLE script and then progressively add and subtract until you find the best possibly combination across all workloads. –  swasheck Nov 9 '12 at 18:14
1  
@swasheck i'm trying to decide between two db's here for terabyte scale dbs. to simply decide that i'm going with one because i cannot reasonably understand why the other doesn't perform well isn't my idea of good decision making! –  headsling Nov 9 '12 at 19:28

Here is your original query

select 
    a.AsOfDate, b.Flag 
from 
    A a inner Join B b on 
        a.BId = b.Id and b.ValidFrom <= a.AsOfDate and b.ValidTo >= a.AsOfDate
where
    a.AsOfDate >= 20110101 and a.AsOfDate <= 20111231

I would suggest refactoring your query in this instance:

select 
    a.AsOfDate, b.Flag 
from
    (
        select * from A
        WHERE AsOfDate >= 20110101
        AND AsOfDate <= 20111231
    ) a INNER JOIN B b ON a.bid=b.id
    AND b.validfrom <= a.asofdate
    AND b.validto   >= a.asofdate
;

That way, the A side's date range ( 20110101 - 20111231 ) gets handled first before the JOIN. An additional benefit of the refactored query is that the JOIN of A and B involves a smaller subset of A.

If you feel uncomfortable with the refactored query, here is another suggestion: switch the range-based WHERE and JOIN clauses

select 
    a.AsOfDate, b.Flag 
from 
    A a inner Join B b on 
        a.BId = b.Id and a.AsOfDate >= 20110101 and a.AsOfDate <= 20111231
where
    b.ValidFrom <= a.AsOfDate and b.ValidTo >= a.AsOfDate

Give it a Try !!!

share|improve this answer
    
unfortunately this doesn't help as the number of records in my test set is still very large (over 900k). –  headsling Nov 15 '12 at 23:11

even though subselects often, not always, perform worse in MySQL you could give the following a try

Select * from
(
select 
    a.AsOfDate,(Select  Flag from b where ValidTo >= a.AsOfDate order by ValidTo  limit 0, 1) as Flag
from 
    A 
where
    a.AsOfDate >= 20110101 and a.AsOfDate <= 20111231
)
where Flag is not NULL 
share|improve this answer
    
While this piece of code may or may not help, code-only answers are highly discouraged on this site. To turn it into a good answer, you should explain why do you think this will help. Thanks in advance! –  dezso Nov 18 '12 at 9:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.