Меню
Что нового?

Помогите оптимизировать запросы к БД

drcrash

Новичок
C:\Program Files\MariaDB 10.1\bin\mysqld.exe, Version: 10.1.14-MariaDB (mariadb.org binary distribution). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
# Time: 160613 23:07:30
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 11 Schema: admin_afru QC_hit: No
# Query_time: 3.353964 Lock_time: 0.000438 Rows_sent: 10 Rows_examined: 44161
# Rows_affected: 0
use admin_afru;
SET timestamp=1465834050;
SELECT p.id, p.date, p.short_story, p.xfields, p.title, p.category, p.alt_name FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE p.approve=1 AND p.date >= '2016-06-13 23:07:26' - INTERVAL 1 MONTH AND p.date < '2016-06-13 23:07:26' ORDER BY rating DESC, comm_num DESC, news_read DESC, date DESC LIMIT 0,10;
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 12 Schema: admin_afru QC_hit: No
# Query_time: 2.398111 Lock_time: 0.000517 Rows_sent: 10 Rows_examined: 44161
# Rows_affected: 0
SET timestamp=1465834050;
SELECT p.id, p.date, p.short_story, p.xfields, p.title, p.category, p.alt_name FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE p.approve=1 AND p.date >= '2016-06-13 23:07:28' - INTERVAL 1 MONTH AND p.date < '2016-06-13 23:07:28' ORDER BY rating DESC, comm_num DESC, news_read DESC, date DESC LIMIT 0,10;
# Time: 160613 23:07:31
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 10 Schema: admin_afru QC_hit: No
# Query_time: 5.052601 Lock_time: 0.003938 Rows_sent: 10 Rows_examined: 44161
# Rows_affected: 0
SET timestamp=1465834051;
SELECT p.id, p.date, p.short_story, p.xfields, p.title, p.category, p.alt_name FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE p.approve=1 AND p.date >= '2016-06-13 23:07:26' - INTERVAL 1 MONTH AND p.date < '2016-06-13 23:07:26' ORDER BY rating DESC, comm_num DESC, news_read DESC, date DESC LIMIT 0,10;
# Time: 160613 23:07:36
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 12 Schema: admin_afru QC_hit: No
# Query_time: 3.907822 Lock_time: 0.000171 Rows_sent: 8 Rows_examined: 15641
# Rows_affected: 0
SET timestamp=1465834056;
SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE category regexp '[[:<:]](2015|2016|2017|2018|2019|2020|2021|2022|2023|2024|2025|2026|2027|2028|2029|2030|2031|2032|2033|2034|2035|2036|2037|2038|2039|2040|2041|2042|2043|2044|2045|2046|2047|2048|2049|2050)[[:>:]]' AND approve=1 ORDER BY rating DESC LIMIT 0,8;
# Time: 160613 23:07:39
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 12 Schema: admin_afru QC_hit: No
# Query_time: 3.168040 Lock_time: 0.000138 Rows_sent: 8 Rows_examined: 14753
# Rows_affected: 0
SET timestamp=1465834059;
SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE category regexp '[[:<:]](2)[[:>:]]' AND approve=1 ORDER BY rating DESC LIMIT 0,8;
# Time: 160613 23:07:43
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 12 Schema: admin_afru QC_hit: No
# Query_time: 3.742753 Lock_time: 0.000144 Rows_sent: 8 Rows_examined: 17627
# Rows_affected: 0
SET timestamp=1465834063;
SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE category regexp '[[:<:]](3)[[:>:]]' AND approve=1 ORDER BY rating DESC LIMIT 0,8;
# Time: 160613 23:07:46
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 12 Schema: admin_afru QC_hit: No
# Query_time: 2.884844 Lock_time: 0.000142 Rows_sent: 8 Rows_examined: 14801
# Rows_affected: 0
SET timestamp=1465834066;
SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE category regexp '[[:<:]](4)[[:>:]]' AND approve=1 ORDER BY rating DESC LIMIT 0,8;
# Time: 160613 23:07:49
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 12 Schema: admin_afru QC_hit: No
# Query_time: 2.992404 Lock_time: 0.000137 Rows_sent: 8 Rows_examined: 14819
# Rows_affected: 0
SET timestamp=1465834069;
SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE category regexp '[[:<:]](5)[[:>:]]' AND approve=1 ORDER BY rating DESC LIMIT 0,8;
# Time: 160613 23:07:52
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 12 Schema: admin_afru QC_hit: No
# Query_time: 2.839354 Lock_time: 0.000151 Rows_sent: 8 Rows_examined: 14825
# Rows_affected: 0
SET timestamp=1465834072;
SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE category regexp '[[:<:]](6)[[:>:]]' AND approve=1 ORDER BY rating DESC LIMIT 0,8;
# Time: 160613 23:07:55
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 12 Schema: admin_afru QC_hit: No
# Query_time: 2.937549 Lock_time: 0.000141 Rows_sent: 8 Rows_examined: 15707
# Rows_affected: 0
SET timestamp=1465834075;
SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE category regexp '[[:<:]](9)[[:>:]]' AND approve=1 ORDER BY rating DESC LIMIT 0,8;
# Time: 160613 23:07:58
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 12 Schema: admin_afru QC_hit: No
# Query_time: 2.963671 Lock_time: 0.000143 Rows_sent: 8 Rows_examined: 24133
# Rows_affected: 0
SET timestamp=1465834078;
SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE category regexp '[[:<:]](24)[[:>:]]' AND approve=1 ORDER BY rating DESC LIMIT 0,8;
# Time: 160613 23:08:32
# User@Host: admin_afru[admin_afru] @ localhost [::1]
# Thread_id: 8 Schema: admin_afru QC_hit: No
# Query_time: 2.013236 Lock_time: 0.000062 Rows_sent: 1 Rows_examined: 14718
# Rows_affected: 0
SET timestamp=1465834112;
SELECT *
FROM `dle_post`
WHERE `kpid` = '21499';

Посмотрел старый топик - http://phpclub.ru/talk/threads/оптимизация-классического-запроса.43177/

попробовал по аналогии добавить индекс, получаю следующее сообщение:

alter table dle_post add index (approve, rating, comm_num, news_read, date)



Ответ MySQL:

#1072 - Ключевой столбец 'rating' в таблице не существует


хотя столбец 'rating' существует(

все в innodb
 
Последнее редактирование:

drcrash

Новичок
Есть в обоих таблицах, попробовал для dle_post_exstras сделать - выдает такую же ошибку (
и да - перевел все таблицы в innodb - но вроде как с поддержкой индексов (они там есть вроде как)
 
Adelf

Adelf

Administrator
Команда форума
Если бы был в обоих таблицах, то этот запрос не работал бы:
Код: 
SELECT p.id, p.date, p.short_story, p.xfields, p.title, p.category, p.alt_name FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE p.approve=1 AND p.date >= '2016-06-13 23:07:26' - INTERVAL 1 MONTH AND p.date < '2016-06-13 23:07:26' ORDER BY rating DESC, comm_num DESC, news_read DESC, date DESC LIMIT 0,10;
 

drcrash

Новичок
А что делать?
вот так:
alter table dle_post_extras add index (approve, rating, comm_num, news_read, date)
выдает тоже самое:
Ошибка
SQL запрос:


alter table dle_post_extras add index (approve, rating, comm_num, news_read, date)



Ответ MySQL:

#1072 - Ключевой столбец 'approve' в таблице не существует

если approve убрать из команды - то такая же ошибка и к другим столбцам, что их нет. Но они есть. Странно
 
Adelf

Adelf

Administrator
Команда форума
Сделай
SHOW CREATE TABLE dle_post
SHOW CREATE TABLE dle_post_extras

и выведи сюда...
 

drcrash

Новичок
CREATE TABLE `dle_post` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`autor` varchar(40) NOT NULL DEFAULT '',
`date` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
`short_story` text NOT NULL,
`full_story` text NOT NULL,
`xfields` text NOT NULL,
`title` varchar(255) NOT NULL DEFAULT '',
`descr` varchar(200) NOT NULL DEFAULT '',
`keywords` text NOT NULL,
`category` varchar(200) NOT NULL DEFAULT '0',
`alt_name` varchar(200) NOT NULL DEFAULT '',
`comm_num` mediumint(8) unsigned NOT NULL DEFAULT '0',
`allow_comm` tinyint(1) NOT NULL DEFAULT '1',
`allow_main` tinyint(1) unsigned NOT NULL DEFAULT '1',
`approve` tinyint(1) NOT NULL DEFAULT '0',
`fixed` tinyint(1) NOT NULL DEFAULT '0',
`allow_br` tinyint(1) NOT NULL DEFAULT '1',
`symbol` varchar(3) NOT NULL DEFAULT '',
`tags` varchar(250) NOT NULL DEFAULT '',
`metatitle` varchar(255) NOT NULL DEFAULT '',
`kpid` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `autor` (`autor`),
KEY `alt_name` (`alt_name`),
KEY `category` (`category`),
KEY `approve` (`approve`),
KEY `allow_main` (`allow_main`),
KEY `date` (`date`),
KEY `symbol` (`symbol`),
KEY `comm_num` (`comm_num`),
KEY `tags` (`tags`),
KEY `fixed` (`fixed`),
FULLTEXT KEY `short_story` (`short_story`,`full_story`,`xfields`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=14851 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

CREATE TABLE `dle_post_extras` (
`eid` int(11) NOT NULL AUTO_INCREMENT,
`news_id` int(11) NOT NULL DEFAULT '0',
`news_read` int(11) NOT NULL DEFAULT '0',
`allow_rate` tinyint(1) NOT NULL DEFAULT '1',
`rating` int(11) NOT NULL DEFAULT '0',
`vote_num` int(11) NOT NULL DEFAULT '0',
`votes` tinyint(1) NOT NULL DEFAULT '0',
`view_edit` tinyint(1) NOT NULL DEFAULT '0',
`disable_index` tinyint(1) NOT NULL DEFAULT '0',
`related_ids` varchar(255) NOT NULL DEFAULT '',
`access` varchar(150) NOT NULL DEFAULT '',
`editdate` int(11) NOT NULL DEFAULT '0',
`editor` varchar(40) NOT NULL DEFAULT '',
`reason` varchar(255) NOT NULL DEFAULT '',
`user_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`eid`),
KEY `news_id` (`news_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14855 DEFAULT CHARSET=utf8mb4
 
Adelf

Adelf

Administrator
Команда форума
Я может не до конца понял, но похоже ты одну таблицу на две разделил. А не стоило.
 

drcrash

Новичок
Таблицы не делил, это в dle так по дефолту... я только пару столбцов добавлял, но они в этом запросе не участвуют. Я так понимаю, что в выборке участвуют две таблицы и в этом проблема создания индекса? Запрос 30 секунд обрабатывается(, а база планируется в 2 раза больше, чем есть сейчас(
 
Adelf

Adelf

Administrator
Команда форума
делай индексы на отдельно поля. по каждой таблице.
 

drcrash

Новичок
То есть
alter table dle_post add index (approve)
alter table dle_post add index (rating)
и тд?
А что за строчки в выводе

KEY `autor` (`autor`),
KEY `alt_name` (`alt_name`),
KEY `category` (`category`),
KEY `approve` (`approve`),
KEY `allow_main` (`allow_main`),
KEY `date` (`date`),
KEY `symbol` (`symbol`),
KEY `comm_num` (`comm_num`),
KEY `tags` (`tags`),
KEY `fixed` (`fixed`),

- это индексы? для этих столбцов не нужно делать?
 

AnrDaemon

Продвинутый новичок
Да, это индексы.
И кстати, делать индексы на поля с низкой кардинальностью бессмысленно.
Вставку будут замедлять, а работу не ускорят.
 
Войдите или зарегистрируйтесь для ответа.
Сверху