-1

I have this DB schema:

DB_Schema

As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table:

SELECT COUNT(*) FROM `territory`; => 6112 rows
SELECT COUNT(*) FROM `group`; => 13669 rows
SELECT COUNT(*) FROM `accountshare`; => 412862 rows
SELECT COUNT(*) FROM `account`; => 206410 rows
SELECT COUNT(*) FROM `address_vod__c`; => 1198746 rows

I am trying to find each record after 2015-07-01 and I am using this query:

SELECT 
    a.id AS aid,
    t.id AS tid,
    t.name AS tname,
    a.lastmodifieddate AS almd,
    ad.lastmodifieddate AS adlmd,
    acs.lastmodifieddate AS acslmd
FROM
    `territory` AS t
        JOIN
    `group` AS g ON (g.relatedid = t.id)
        JOIN
    `accountshare` AS acs ON (acs.userorgroupid = g.id)
        JOIN
    `account` AS a ON (a.id = acs.accountid)
        JOIN
    `address_vod__c` AS ad ON (ad.account_vod__c = a.id
        AND ad.primary_vod__c = 1)
WHERE
    a.lastmodifieddate > '2015-07-01'
        OR ad.lastmodifieddate > '2015-07-01'
        OR acs.lastmodifieddate > '2015-07-01'
        OR g.lastmodifieddate > '2015-07-01'
ORDER BY tid DESC;

But it's taking an eternity because the large size of the resultsets. I've tried to separate in two queries and then JOIN them but is the same takes to much time:

SELECT 
    *
FROM
    (SELECT 
        a.id AS aid,
            ad.id AS adid,
            a.lastmodifieddate AS almd,
            ad.lastmodifieddate AS adlmd,
            ad.primary_vod__c AS adp
    FROM
        account AS a
    JOIN address_vod__c AS ad ON (ad.account_vod__c = a.id
        AND ad.primary_vod__c = 1)
    WHERE
        a.lastmodifieddate > '2015-07-01'
            OR ad.lastmodifieddate > '2015-07-01') AS q1
        JOIN
    (SELECT 
        acs.accountid AS aid,
            t.id AS tid,
            t.name AS tname,
            acs.lastmodifieddate AS acslmd,
            g.lastmodifieddate AS glmd
    FROM
        `territory` AS t
    JOIN `group` AS g ON (g.relatedid = t.id)
    JOIN `accountshare` AS acs ON (acs.userorgroupid = g.id)
    WHERE
        acs.lastmodifieddate > '2015-07-01'
            OR g.lastmodifieddate > '2015-07-01') AS q2 ON q1.aid = q2.aid;

In this case the first query returns all the account and address records updated since a specific date (2015-07-01) and the second query returns all the accountshare changes updated since a specific date (2015-07-01) and I need accountshare because that table along with group match targets to territories.

  • 1
    I'm confused. Why do you join if there are no relationships? – ypercubeᵀᴹ Aug 9 '15 at 14:30
  • @ypercube because I am trying to get the results in just one query, otherwise I will end doing in separate queries and a lot of programmatic on the server side – ReynierPM Aug 9 '15 at 14:31
  • 1
    So you want the database engine to somehow make sense of completely unrelated tables so you can save some work in code? Wrong solution for the wrong problems – Aaron Bertrand Aug 9 '15 at 15:30
  • @AaronBertrand so what is your suggestion? Rely on code? I can do that I am just look for a better way and yes save some time. If you have any better idea than code then let me know and I see if I can apply here (can't join tables at all, can give this a try, but I am not so sure the script that handle DB population will works after create any relationship) – ReynierPM Aug 9 '15 at 15:37
  • 1
    Then you do INSERT table1 SELECT new data FROM table1; repeat for table 2, table 3, etc. expecting some kind of efficiency from doing these things all at once instead of having 5 different queries is like trying to eat all your meals for the week in one sitting. – Aaron Bertrand Aug 9 '15 at 16:44
0

I suggest you to try with the APPLY join operator. It is useful when you have to join tables where one table has much more rows than the other tables.

it's taking an eternity because the large size of the resultsets

Which indexes are involved on the execution plan of the query? Can you post the execution plan used for the original query?

Thanks

  • 2
    I don't know if this would make any change if this were SQL Server, but as they are using MariaDB, they have no chance of even trying your suggestion: there is no APPLY in MariaDB's variety of SQL. – Andriy M Aug 9 '15 at 15:07
  • 1
    Yes, I just saw the same, there isn't APPLY on MariaDB/MySQL and what you mean with INDEXES? Those tables are very basic so I don't think they have any column indexed if this is what you are asking me – ReynierPM Aug 9 '15 at 15:08
  • 2
    @ReynierPM: And what do you mean by "Those tables are very basic"? Or what does that have to do with indexing? – Andriy M Aug 9 '15 at 15:14
  • @AndriyM I mean there is nothing indexed at all other than PK. Take a look here to account table and you'll see what I am talking about – ReynierPM Aug 9 '15 at 15:18
0

You can split the query by each <table>.lastmodifieddate > '2015-07-01' and then use UNION to run them all at once.

Regarding some comments about indexing - indexes are crucial to fast query execution, it is not about complexity of a table but about number of rows - small table can be easily kept in memory but large one cannot so index can help to fast locate needed rows.

You should at least create index on (lastmodifieddate) for each table where it is checked - that and the splitting with UNION can make the query fast unless lastmodifieddate > '2015-07-01'is TRUE for too many rows.

Read this slide share to learn why indexes are needed and how to decide what to index.

Example rewrite for UNION:

SELECT 
    a.id AS aid,
    t.id AS tid,
    t.name AS tname,
    a.lastmodifieddate AS almd,
    ad.lastmodifieddate AS adlmd,
    acs.lastmodifieddate AS acslmd
FROM
    `territory` AS t
        JOIN
    `group` AS g ON (g.relatedid = t.id)
        JOIN
    `accountshare` AS acs ON (acs.userorgroupid = g.id)
        JOIN
    `account` AS a ON (a.id = acs.accountid)
        JOIN
    `address_vod__c` AS ad ON (ad.account_vod__c = a.id
        AND ad.primary_vod__c = 1)
WHERE
    a.lastmodifieddate > '2015-07-01'
UNION
SELECT 
    a.id AS aid,
    t.id AS tid,
    t.name AS tname,
    a.lastmodifieddate AS almd,
    ad.lastmodifieddate AS adlmd,
    acs.lastmodifieddate AS acslmd
FROM
    `territory` AS t
        JOIN
    `group` AS g ON (g.relatedid = t.id)
        JOIN
    `accountshare` AS acs ON (acs.userorgroupid = g.id)
        JOIN
    `account` AS a ON (a.id = acs.accountid)
        JOIN
    `address_vod__c` AS ad ON (ad.account_vod__c = a.id
        AND ad.primary_vod__c = 1)
WHERE
        ad.lastmodifieddate > '2015-07-01'
UNION
SELECT 
    a.id AS aid,
    t.id AS tid,
    t.name AS tname,
    a.lastmodifieddate AS almd,
    ad.lastmodifieddate AS adlmd,
    acs.lastmodifieddate AS acslmd
FROM
    `territory` AS t
        JOIN
    `group` AS g ON (g.relatedid = t.id)
        JOIN
    `accountshare` AS acs ON (acs.userorgroupid = g.id)
        JOIN
    `account` AS a ON (a.id = acs.accountid)
        JOIN
    `address_vod__c` AS ad ON (ad.account_vod__c = a.id
        AND ad.primary_vod__c = 1)
WHERE
    acs.lastmodifieddate > '2015-07-01'
UNION
SELECT 
    a.id AS aid,
    t.id AS tid,
    t.name AS tname,
    a.lastmodifieddate AS almd,
    ad.lastmodifieddate AS adlmd,
    acs.lastmodifieddate AS acslmd
FROM
    `territory` AS t
        JOIN
    `group` AS g ON (g.relatedid = t.id)
        JOIN
    `accountshare` AS acs ON (acs.userorgroupid = g.id)
        JOIN
    `account` AS a ON (a.id = acs.accountid)
        JOIN
    `address_vod__c` AS ad ON (ad.account_vod__c = a.id
        AND ad.primary_vod__c = 1)
WHERE
    g.lastmodifieddate > '2015-07-01'
ORDER BY tid DESC;
  • Can you put an example of UNION? I'm not sure how to achieve this using that sentence – ReynierPM Aug 9 '15 at 16:15
  • Check code in the edit - you make the same query just with different part of WHERE condition each time - it MIGHT be faster than single one even without the indexes, but you should definitely add them. – jkavalik Aug 9 '15 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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