MySQL SQL left join with force index where clause

Question

SELECT
partner_id, click_date, sum(clicks) as clicks
FROM
daily_metric
LEFT JOIN transaction ON daily_metric.partner_id = transaction.partner_id
FORCE INDEX (click_date_index) WHERE click_date BETWEEN :ycp0 AND :ycp1
GROUP BY partner_id , click_date)

I have wrond syntacs :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FORCE INDEX (click_date_index) WHERE click_date BETWEEN '2014-09-01' AND '2014-0' at line 26.

Index is used to where clause, what is wrong with my syntacs ?

Rahul Tripathi · Accepted Answer · 2014-09-12 07:18:13Z

1

Try this since he FORCE/USE/IGNORE goes after the table name you are joining:

SELECT
partner_id, click_date, sum(clicks) as clicks
FROM
daily_metric
LEFT JOIN transaction
FORCE INDEX FOR JOIN (click_date_index) ON daily_metric.partner_id = transaction.partner_id 
WHERE click_date BETWEEN :ycp0 AND :ycp1
GROUP BY partner_id , click_date)

answered Sep 12, 2014 at 7:18

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wizard Over a year ago

You understand not correct this index is NOT for Join, it's for daily_metric table

Collectives™ on Stack Overflow

MySQL SQL left join with force index where clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related