it’s next to impossible to search the site for problems related to the Where…in() clause, because “In” is a common word. Does anyone have any good resources for reading about how to deal with where…in() queries? I’m trying to optimize a query.
Put this into google: “in clause” site:mysqlperformanceblog.com -title:“search results”
What kind of IN-clause is it (subquery or large explicit list)?
Thanks, that’s very helpful advice.
My problem was that some joker was adding strings into the list of numbers in our IN() clause, causing a table scan:
SELECT s.*
FROM (source AS s)
INNER JOIN merchant AS m ON s.source_id = m.source_id
INNER JOIN offer as of ON m.merchant_id = of.merchant_id
WHERE of.offer_id IN (3969171, 3969172, 3969173, 3969174, 3969175, 3969176, 3969177, 3969178, 3969179, 3969180, 3969181, 3969182, 3969183, 3969184, 3969185, 3969186, 3969187, 3969188, 3969189, 3969190, 3969191, 3969192, 3969193, 3969194, 3969195, 3969196, 3969197, 3969198, ‘3569701’, 3969199, 3969200, 3969201, 3969202, 3969203, 3969204, 3969205, 3969206, 3969207, 3969208, 3969209, 3969210, 3969211, 3969212, 3969213, 3969214, 3969215, 3969216, 3969217, 3969218, 3969219, 3969220, 3969221, 3969222, 3969223, 3969224, 3969225, 3969226, 3969227, 3969228, 3969229, 3969230, 3969231, 3969232, 3969233, 3969234, 3969235, 3969236, 3969237, 3969238, 3969239, 3969240, 3969241, 3969242, 3969243, 3969244, 3969245, 3969246, 3969247, 3969248, 3969249, 3969250, 3969251, 3969252, 3969253, 3969254, 3969255, 3969256, 3969257, 3969258, 3969259, 3969260, 3969261, 3969262, 3969263, 3969264, 3969265, 3969266, 3969267, 3969268, 3969269, 3969270, 3969271, 3969272, 3969273, 3969274, 3969275, 3969276, 3969277)