Как реализовать уникальный индекс для keywords в url_alias при использовании PARTITION BY HASH ?

[Dimasscus]

Всем привет. столкнулся с проблемой- при импорте товаров в магазин получаю дубли url_alias

таблица использует PARTITION BY HASH (url_alias_id)

(фото1)

 

нагуглил что для рещение проблемы дублей нужно использовать уникальный индекс для keyword

но- при попытке выполнить запрос  

 

ALTER TABLE `oc_url_alias` ADD UNIQUE `keyword` (`keyword`);

получаю ошибку   #1061 - Дублирующееся имя ключа 'keyword'

(фото 2)

 

если же патаыюсь задать индекс в настройках таблицы  - (фото3) 

 

то получаю ошибку #1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

гуглом нашел что нужно как то связать данные , но как -не хватает соображалки

буду благодарен за любую помощь

[php]

3 минуты назад, Dimasscus сказал:

ALTER TABLE `oc_url_alias` ADD UNIQUE `keyword` (`keyword`);

вы сначало дубли удалите, а потом запрос отправляйте

сами дубли можно найти так

select `keyword` , count(*) from `oc_url_alias` group by `keyword` having count(*) > 1;

 

Змінено 13 листопада 2020 користувачем php

[Dimasscus]

10 минут назад, php сказал:

вы сначало дубли удалите, а потом запрос отправляйте

сами дубли можно найти так

select `keyword` , count(*) from `oc_url_alias` group by `keyword` having count(*) > 1;

 

https://clip2net.com/s/49NWHtP дублей нет. а результат тот же 

 #1061 - Дублирующееся имя ключа 'keyword'

[php]

сможете в личку прислать экспорт таблицы oc_url_alias

[Dimasscus]

пробую так ALTER TABLE `oc_url_alias` DROP INDEX `keyword`, ADD UNIQUE `keyword` (`keyword`, `url_alias_id`) USING BTREE;

запрос вроде выполняется, но при запуске импорта вижу что дубли появляются вновь

[Dimasscus]

2 минуты назад, php сказал:

сможете в личку прислать экспорт таблицы oc_url_alias

шлю

[chukcha]

Стесняюсь спросить а зачем там партиции

[100napb]

8 hours ago, Dimasscus said:

то получаю ошибку #1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

должна быть уникальность в таблице по тем полям, которые используются для определения партиций.

подробнее тут
 

Что бы на корню убрать дубли, проще всего сделать primary key (keyword, url_alias_id)

Spoiler

CREATE TABLE oc_url_alias (
  url_alias_id int(11) NOT NULL AUTO_INCREMENT,
  query varchar(255) NOT NULL,
  keyword varchar(255) NOT NULL,
  PRIMARY KEY (keyword, url_alias_id)
)
ENGINE = MYISAM,
AUTO_INCREMENT = 25511,
AVG_ROW_LENGTH = 59,
CHARACTER SET utf8,
CHECKSUM = 0,
COLLATE utf8_general_ci
PARTITION BY HASH (url_alias_id)
(
PARTITION partition1 ENGINE = MYISAM,
PARTITION partition2 ENGINE = MYISAM,
PARTITION partition3 ENGINE = MYISAM

....
);

 

 

37 minutes ago, chukcha said:

Стесняюсь спросить а зачем там партиции

присоединяюсь в вопросу. в данном случае, секционирование таблиц потенциально не принесет никакого профита по сравнению с обычным индексом.

Змінено 13 листопада 2020 користувачем 100napb

[chukcha]

  query varchar(255) NOT NULL,
  keyword varchar(255) NOT NULL,

 

 

лучше в char

Если сеопро с кешем - пофик


url_alias_id нигде не используется, только для автоинкремента ( и для редакторово)

 

[Dimasscus]

2 часа назад, chukcha сказал:

Стесняюсь спросить а зачем там партиции

Єто решение внедрил разработчик скриптов импорта. Ссилка с товарами весит более 2 гб. Товаров около 300к. До того как он єто внедрил- тормозил сайт жутко, и вигрузка тоже медлено шла. Єто решение каким то образом ускорило работу вигрузки, обновления, да и самого сайта. Но вот образовалась проблема дублей

[chukcha]

18 минут назад, Dimasscus сказал:

Товаров около 300к.

И сел про?

Выгрузка?

 

хм... не понимать

[Dimasscus]

2 минуты назад, chukcha сказал:

И сел про?

Выгрузка?

 

хм... не понимать

на сайт импортируются товары из ссылки поставщика. товаров овердофига да и сама ссылка весит естественно также. импорт реализован в несколько этапов- по cron-у грузим порции товаров-по 500 позиций в час. паралельно грузим категории. и отдельно- наличие, цену, фотки, и генерируем чпу. генерация чпу как по мне- реализована через ж, но пока туда не вникал, только пытаюсь разбираться

вот код генерации чпу в скрипте импорта 

   $querys2 = "SELECT url_alias_id FROM oc_url_alias WHERE query = 'product_id=".$results['product_id']."'";
                $results2 = mysqli_query($link, $querys2);
                $results2 = mysqli_fetch_array($results2);

                if ($results2['url_alias_id']!=''){}else{
                  	$s = $data['NAME'];
                  	$s = (string) $s; // преобразуем в строковое значение
                  	$s = strip_tags($s); // убираем HTML-теги
                  	$s = str_replace(array("\n", "\r"), " ", $s); // убираем перевод каретки
                  	$s = preg_replace("/\s+/", ' ', $s); // удаляем повторяющие пробелы
                  	$s = trim($s); // убираем пробелы в начале и конце строки
                  	$s = function_exists('mb_strtolower') ? mb_strtolower($s) : strtolower($s); // переводим строку в нижний регистр (иногда надо задать локаль)
                  	$s = strtr($s, array('а'=>'a','б'=>'b','в'=>'v','г'=>'g','д'=>'d','е'=>'e','ё'=>'e','ж'=>'j','з'=>'z','и'=>'i','й'=>'y','к'=>'k','л'=>'l','м'=>'m','н'=>'n','о'=>'o','п'=>'p','р'=>'r','с'=>'s','т'=>'t','у'=>'u','ф'=>'f','х'=>'h','ц'=>'c','ч'=>'ch','ш'=>'sh','щ'=>'shch','ы'=>'y','э'=>'e','ю'=>'yu','я'=>'ya','ъ'=>'','ь'=>''));
                  	$s = preg_replace("/[^0-9a-z-_ ]/i", "", $s); // очищаем строку от недопустимых символов
                  	$s = str_replace(" ", "-", $s); // заменяем пробелы знаком минус

                    mysqli_query($link, "INSERT INTO oc_url_alias SET query = 'product_id=".$results['product_id']."', keyword = '". $s ."'");
                }

насколько я понимаю- он использует url_alias_id как уникальное поле. почему так- тоже вопрос :))

но в данный момент хотелось бы чтоб не плодило дубли чпу. при чем, повторюсь, дубли товаров не создает- только дубли чпу в бд url_alias, что приводит к ошибке- страница не найдена, и другим траблам.

в бд я попытался связать, как писали выше, два значения,  https://clip2net.com/s/49OlmCx но что то не то. знаний не хватает сделать правильно.

[Dimasscus]

2 часа назад, 100napb сказал:

должна быть уникальность в таблице по тем полям, которые используются для определения партиций.

подробнее тут
 

Что бы на корню убрать дубли, проще всего сделать primary key (keyword, url_alias_id)

  Скрыть контент

CREATE TABLE oc_url_alias (
  url_alias_id int(11) NOT NULL AUTO_INCREMENT,
  query varchar(255) NOT NULL,
  keyword varchar(255) NOT NULL,
  PRIMARY KEY (keyword, url_alias_id)
)
ENGINE = MYISAM,
AUTO_INCREMENT = 25511,
AVG_ROW_LENGTH = 59,
CHARACTER SET utf8,
CHECKSUM = 0,
COLLATE utf8_general_ci
PARTITION BY HASH (url_alias_id)
(
PARTITION partition1 ENGINE = MYISAM,
PARTITION partition2 ENGINE = MYISAM,
PARTITION partition3 ENGINE = MYISAM

....
);

 

 

присоединяюсь в вопросу. в данном случае, секционирование таблиц потенциально не принесет никакого профита по сравнению с обычным индексом.

вот вроде получилось https://clip2net.com/s/49OmnGR

но не пойму как у url_alias_id теперь убрать первичный 

[chukcha]

21 минуту назад, Dimasscus сказал:

но в данный момент хотелось бы чтоб не плодило дубли чпу. при чем, повторюсь, дубли товаров не создает- только дубли чпу в бд url_alias, что приводит к ошибке- страница не найдена, и другим траблам.

ППЦ
без маскирования
Теперь понятно почему

 

Правильно было бы делете инсерт

Автоинкремент при частых обновлениях не справится

 

транслитерация - ПЕСТНЯ


if ($results2['url_alias_id']!='')

Это полный зззззз...ццц
 

 

[Dimasscus]

2 минуты назад, chukcha сказал:

ППЦ
без маскирования
Теперь понятно почему

 

Правильно было бы делете инсерт

Автоинкремент при частых обновлениях не справится

 

транслитерация - ПЕСТНЯ


if ($results2['url_alias_id']!='')

Это полный зззззз...ццц
 

 

Я вот тоже и ржал и плакал обновременно. хотя я и не спец- но даже мне видна глубина преисподней в этом куске гуано кода

щас переделывать буду

[chukcha]

Даже при таком подходе
автоинкремент  это  нормально

 

Т.е.
Если он проверяет наличиие  и если нет, то инсертае

 

можно сделать так

 

Склонировать таблицу

Затем удалить таблицу
Создать дефолтную из инстала

Добавить unique

Truncate

INSERT into url_alias (query,keyword) SELECT query, keyword FROM clone

 

Вы получите бездырочный id
c primary key

А потмо повторно разобраться со скорстью

Если у вас синхронизация - частая и долгая, то оставить innodb
иначе майИсам

 

 

[Dimasscus]

16 минут назад, chukcha сказал:

Даже при таком подходе
автоинкремент  это  нормально

 

Т.е.
Если он проверяет наличиие  и если нет, то инсертае

 

можно сделать так

 

Склонировать таблицу

Затем удалить таблицу
Создать дефолтную из инстала

Добавить unique

Truncate

INSERT into url_alias (query,keyword) SELECT query, keyword FROM clone

 

Вы получите бездырочный id
c primary key

А потмо повторно разобраться со скорстью

Если у вас синхронизация - частая и долгая, то оставить innodb
иначе майИсам

 

 

спасибо. интересное решение. попробую.

[Dimasscus]

5 часов назад, chukcha сказал:

Даже при таком подходе
автоинкремент  это  нормально

 

Т.е.
Если он проверяет наличиие  и если нет, то инсертае

 

можно сделать так

 

Склонировать таблицу

Затем удалить таблицу
Создать дефолтную из инстала

Добавить unique

Truncate

INSERT into url_alias (query,keyword) SELECT query, keyword FROM clone

 

Вы получите бездырочный id
c primary key

А потмо повторно разобраться со скорстью

Если у вас синхронизация - частая и долгая, то оставить innodb
иначе майИсам

 

 

еще один вопрос

вот тут проверка на существование url_alias_id 

if ($results2['url_alias_id']!=''){}else{

может есть смысл проверять все же существование keyword ?

по принципу -если не существует- то создать?

 

[Dimasscus]

5 часов назад, Dimasscus сказал:

спасибо. интересное решение. попробую.

касательно предложенного решения

не работает. точнее работает но не так :))

а именно- 

если после создания дефолтной таблицы выполнить  указанный Вами запрос не добавляя уникальный индекс предварительно в новой таблице- данные переносятся в таблицу.

если же добавить предварительно перед выполнением запроса уникальный индекс- то получаю https://clip2net.com/s/49OGY6J

пусто вообщем

хз что делать с этим всем...

[chukcha]

6 часов назад, Dimasscus сказал:

INSERT into url_alias (query,keyword) SELECT query, keyword FROM clone

А что у вас?

[Dimasscus]

INSERT into oc_url_alias_clone (query,keyword) SELECT query, keyword FROM oc_url_alias_clone2

где oc_url_alias_clone -это клонированная таблица, а oc_url_alias_clone2 таблица созданная 

Змінено 13 листопада 2020 користувачем Dimasscus

[chukcha]

это вы для експеримента?
А что такое  c_url_alias_clone2

Там есть записи?

[chukcha]

INSERT into oc_url_alias_clone (query,keyword) SELECT query, keyword FROM oc_url_alias_clone2

 

oc_url_alias_clone - эта должна быть пустая!!!
 

 

oc_url_alias_clone2  - в этой  все что было в url_alias

[Dimasscus]

3 минуты назад, chukcha сказал:

это вы для експеримента?
А что такое  c_url_alias_clone2

Там есть записи?

да, для експеремента. щас бекап откачу попробую снова

[php]

если хотите убрать дыры url_alias_id

то это можно сделать так

SET @i = 0;
UPDATE `os_url_alias` SET `url_alias_id` = (@i := @i + 1) ORDER BY `url_alias_id` ASC;