Как лучше спроектировать БД?

Индексы по a_id и b_id проставить и нормально будут джоины по производительности

Уж точно лучше лайков

даже если таких join будет штук 10, а в основной таблице A под 200 000 записей и в таблице AB под миллион записей?

Ну, во первых, вам никто не мешает забить таблицу данными (рандом) и проверить

Во вторых, вот запрос, скажем, отрабатывает практически моментально на нормально настроенной базе. По количеству записей - category около 1000, products 25 000, product_catnum около 10 000, partsCategories 1 500 000, coordinates около двух миллионов

		SELECT DISTINCT `i`.`id` as image_id,
				(select
                             cats2.`title`
                        from
                             category as cats1
                        inner join
                             category as cats2 on cats1.modelid = cats2.id
                        where
                             cats1.`id` = `i`.CategoryID
                        limit 1) as model_title,
				(select `text` from textinfo where id =`i`.`nameID` limit 1) AS title
				FROM (`products` p)
				JOIN `product_catnum` pc ON `pc`.`product_id` = `p`.`id`
				JOIN `partsCategories` pct ON `pct`.`catalog_num` = `pc`.`part_id`
				JOIN `coordinates` c ON `c`.`partid` = `pct`.`id`
				JOIN `images` i ON `i`.`id` = `c`.`imageID`
				WHERE `p`.`id` =  ' . $id

JOIN будет в любом случае работать быстрее LIKE, а если джойнить по индексам, то тут вообще без комментариев. Вам должно быть абсолютно пофиг на количество записей, если юзаются числовые индексы, главное на проблемы с LIMIT при большом отступе не напороться.

Не совсем понятно, почему одного JOIN не достаточно?

LEFT JOIN AB ON (b1.id=a.id)

WHERE `AB.value`in ('value1', 'value2', 'value3')

Как вам выше написали - просто нужно правильно составить запрос. У вас свзят много-ко-многим - это вполне нормальная и работающая связь, проблем с ней не будет даже при миллионных записях, но запросы нужно будет составлять правильно, ез повторяющихся джойнов.

Самым лучшим будет вариант:

JOIN-ы по many-to-many таблицам работают гораздо быстрее чем запросы с LIKE. Для еще большего ускорения можно разбить один сложный запрос c JOIN-ами на несколько более простых. Грубо говоря, сначала вы получаете список ID пользователей, которые содержат нужные опции, а потом производите по ним выборку. Подобный подход применяется в Yii2.

Подобный подход применяется при выборках с LIMIT OFFSET

dma84, будьте поаккуратней с limit offset. на больших значениях там начинается полный П. я однажды полдня потратил на поиски бага. http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down

Я использую JOIN или переменную-счётчик записей, но во втором случае обязательным условием является наличие всех столбцов в WHERE в составном индексе.