0 Replies Latest reply on Jan 31, 2012 9:20 AM by Dusty G.

    Using MySQL Indexes

    Dusty G.

      Hello,

        I'm currently utilizing a live data connection to a MySQL database.  The query that tableau is generating is not utilizing the optimal indexes and I'm struggling to figure out how to get tableau to tell the database to use a certain index without using a custom SQL.  As soon as I use a custom SQL I lose a bunch of the date selection functionality.  Here is the query that the view is generating:

       

      SELECT `vdat_3207`.`adintegrationid` AS `none_adintegrationid_nk`,

        `partneradintegration_dim`.`adintegrationname` AS `none_adintegrationname_nk`,

        SUM(`vdat_3207`.`sessioncount`) AS `sum_sessioncount_qk`,

        (SUM(`vdat_3207`.`adserverclick`) / SUM(`vdat_3207`.`adserverimpression`)) AS `usr_Engagement Rate_qk`

      FROM `vdat_3207`

      -- USE INDEX (adintegrationid)

        LEFT JOIN `partneradintegration_dim` ON (`partneradintegration_dim`.`adintegrationid`=`vdat_3207`.`adintegrationid`)

      WHERE ((`partneradintegration_dim`.`partnerid` = _latin1 'ap015434934a34970c') AND ((`vdat_3207`.`increment_day` >= DATE_ADD(ADDDATE(FROM_DAYS( TO_DAYS(NOW()) - (DAYOFWEEK(NOW()) - 1) ), INTERVAL 0 SECOND ), INTERVAL (7 * (-3)) DAY)) AND (`vdat_3207`.`increment_day` < DATE_ADD(ADDDATE(FROM_DAYS( TO_DAYS(NOW()) - (DAYOFWEEK(NOW()) - 1) ), INTERVAL 0 SECOND ), INTERVAL (7 * 1) DAY))))

      GROUP BY 1,2

       

       

      VDAT 3204 is a very large table (5M rows) and indexed on adintegrationid

      VDAT_3061 is a smallish table (2K rows) and indexed on partnerid and adintegrationid

       

       

      Yet when the above query is submitted it uses the index from the small table and the query takes more than 5 minutes to complete.  If I simply put a MySQL hint in the query of "USE INDEX (adintegrationid)" the below query finishes sub second.

       

      SELECT `vdat_3207`.`adintegrationid` AS `none_adintegrationid_nk`,

        `partneradintegration_dim`.`adintegrationname` AS `none_adintegrationname_nk`,

        SUM(`vdat_3207`.`sessioncount`) AS `sum_sessioncount_qk`,

        (SUM(`vdat_3207`.`adserverclick`) / SUM(`vdat_3207`.`adserverimpression`)) AS `usr_Engagement Rate_qk`

      FROM `vdat_3207`

      USE INDEX (adintegrationid)

        LEFT JOIN `partneradintegration_dim` ON (`partneradintegration_dim`.`adintegrationid`=`vdat_3207`.`adintegrationid`)

      WHERE ((`partneradintegration_dim`.`partnerid` = _latin1 'ap015434934a34970c') AND ((`vdat_3207`.`increment_day` >= DATE_ADD(ADDDATE(FROM_DAYS( TO_DAYS(NOW()) - (DAYOFWEEK(NOW()) - 1) ), INTERVAL 0 SECOND ), INTERVAL (7 * (-3)) DAY)) AND (`vdat_3207`.`increment_day` < DATE_ADD(ADDDATE(FROM_DAYS( TO_DAYS(NOW()) - (DAYOFWEEK(NOW()) - 1) ), INTERVAL 0 SECOND ), INTERVAL (7 * 1) DAY))))

      GROUP BY 1,2

       

       

      My question is how do I either use a custom SQL yet still have the ability to use a quick filter for the date selection OR how do I force the database or tableau to use the appropriate indexes without modifying the SQL?  My final option will be to adjust the underlying data model…but this seems a bit silly considering if I can make it select the correct index it performs beautifully.