Форум пользователей MySQL

FiMko · 24.03.2010 22:30:01

Ребята, привет всем!

Да, вопрос банален и замучен, но никак не могу найти подходящий ответ ни в сети, ни тем более в самоучителе. Не нашел также и на этом форуме.

DROP PROCEDURE IF EXISTS getAllPairsId;

DELIMITER |

CREATE PROCEDURE getAllPairsId (word varchar(64), lng_id INT)

BEGIN

    select pair_id, phrase1_id, phrase2_id, pair_class, lp_id, pair_date, dict_id, uid, pair_comment from pairs, (

        call getAllPhrasesId(word, lng_id)

    ) a where pairs.phrase1_id = a.phrase_id;

END|

ERROR 1064 (42000): 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 'call getAllPhrasesId(word, lng_id)

        ) a where pairs.phrase1_id = a.phrase_id;

EN' at line 4

Пробовал exec getAllPhrasesId..., execute getAllPhrasesId... не получается.
Подскажите, пожалуйста.

Отредактированно FiMko (24.03.2010 22:52:14)

vasya · 24.03.2010 23:52:11

Ошибка не в вызове процедуры из другой процедуры, а в использовании call внутри select.
Перепишите, чтобы процедура getAllPhrasesId создавала временную таблицу, которую вы сможете использовать далее в запросе SELECT.

FiMko · 24.03.2010 23:55:06

vasya написал:
Ошибка не в вызове процедуры из другой процедуры, а в использовании call внутри select.
Перепишите, чтобы процедура getAllPhrasesId создавала временную таблицу, которую вы сможете использовать далее в запросе SELECT.

Я боялся, что Вы поставите подобный диагноз. Гугл мне намекал тем же самым. Временные таблицы я не хотел использовать даже для того, чтобы избавиться от так называемых derived таблиц, которые не используют индекс. Не буду использовать временные таблицы и сейчас, обойдусь значит без хранимых процедур...

Спасибо за ответ!

Отредактированно FiMko (24.03.2010 23:55:27)

vasya · 24.03.2010 23:58:09

А чем вызвана такая не любовь к временным таблицам?
Не в том смысле, что я предлагаю именно так и поступить, просто любопытно для расширения кругозора

FiMko · 25.03.2010 00:17:01

vasya написал:
А чем вызвана такая не любовь к временным таблицам?
Не в том смысле, что я предлагаю именно так и поступить, просто любопытно для расширения кругозора

Ну в итоге моих чтений разного рода информации по сети, я выяснил, что подзапрос (subquery) создаст таблицы в памяти (если они, конечно, там помещаются), в случае с временными таблицами и типом базы MyISAM, таблицы будут скидываться на жесткий диск. Где-то в сети (сейчас пытался найти, но не нашел...) описывался порядок действий, необходимых для выполнения движком, чтобы создать и обработать временную таблицу. Список, помнится, состоял чуть не из шести шагов. В общем, закрепилось у меня мнение, что производительность при использовании временных таблиц страдает. С удовольствием выслушал бы ваше мнение по этому поводу...

Вот нашел, кажется, по теме: Eliminate the Use of Temporary Tables For HUGE Performance Gains

Отредактированно FiMko (25.03.2010 00:20:56)

paulus · 25.03.2010 00:24:44

Преждевременная оптимизация — корень зла

Подумайте, сколько шагов понадобится серверу для выполнения Ваших
двух хранимых процедур (да еще и вызываемых одна изнутри другой).

FiMko · 25.03.2010 00:31:59

paulus написал:
Преждевременная оптимизация — корень зла

Абсолютно согласен. Но это тоже в разных случаях по-разному... Один раз вот уже, к сожалению, ожегся на проблемах со структурой БД, проблема была очевидной. Пришлось вносить значительные изменения и в базу и в php скрипты. Теперь вот постоянно пытаюсь заглядывать немного вперед. Сказывается и отсутствие опыта в MySQL

paulus написал:
Подумайте, сколько шагов понадобится серверу для выполнения Ваших
двух хранимых процедур (да еще и вызываемых одна изнутри другой).

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

LazY · 25.03.2010 05:56:02

Обычно спасает создавать временные таблицы с ENGINE=MEMORY
(вернее, по-моему, так надо делать вообще всегда; если таблица слишком большая, значит, она неправильно спроектирована; кто не согласен - пусть первый бросит в меня камень).

vasya · 25.03.2010 06:04:36

Ещё нужно обязательно следить, чтобы во временной таблице не было полей типа TEXT или BLOB, иначе она будет записана на диск, а не в память независимо от ENGINE.

FiMko · 25.03.2010 10:34:31

LazY написал:
Обычно спасает создавать временные таблицы с ENGINE=MEMORY
(вернее, по-моему, так надо делать вообще всегда; если таблица слишком большая, значит, она неправильно спроектирована; кто не согласен - пусть первый бросит в меня камень).

Да, именно так я и предполагал. Предстоит, однако, убедиться, что мой хостинг разрешает создавать таблицы такого типа, мало ли чего... А таблицы у меня должны быть весьма небольшого размера.

paulus написал:
Ещё нужно обязательно следить, чтобы во временной таблице не было полей типа TEXT или BLOB, иначе она будет записана на диск, а не в память независимо от ENGINE.

Видел что-то про это в сети, спасибо, что напомнили! У меня varchar, int, tinyint, smallint.

Но вот только непонятно, а стоит ли оно того - переходить на хранимые процедуры. Ибо:
1.

paulus написал:
Подумайте, сколько шагов понадобится серверу для выполнения Ваших двух хранимых процедур (да еще и вызываемых одна изнутри другой).

2. Stored Procedures are EVIL

Почему я вообще хотел попробовать хранимые процедуры?
1. Запрос у меня получился довольно объемный (около 10 строк с форматированием), использование хранимых процедур в моем случае улучшило бы в некотором роде читаемость MySQL кода. Читать говорящие названия процедур удобнее...

2. Более удобное переиспользование MySQL кода. Фрагменты кода (примитивы) используются из различных мест и для различной функциональности. Если перевести такие участки кода в хранимые процедуры, то а) любое изменение в процедуре на автомате подхватывается во всех местах, где она используется б) само переиспользование (перенос) MySQL становится более удобным.

Можно заметить, что упор в основном на удобство написания MySQL кода, остается на практическом опыте проверить не в ущерб ли это будет производительности.

Отредактированно FiMko (25.03.2010 10:41:07)

FiMko · 26.03.2010 23:23:27

LazY написал:
Обычно спасает создавать временные таблицы с ENGINE=MEMORY

Подскажите, пожалуйста, почему не используется индекс.

CREATE TABLE test TYPE=MEMORY select phrase_id, order_id, phrases.word_id from phrases, (

    select word_id from words where word in ("dog", "anchor")

) a where phrases.word_id=a.word_id;

select * from test;

+-----------+----------+---------+

| phrase_id | order_id | word_id |

+-----------+----------+---------+

|         1 |        1 |       1 |

|         5 |        1 |       1 |

|         9 |        2 |       1 |

|         5 |        2 |       5 |

+-----------+----------+---------+

alter table test add index (phrase_id);

alter table test add index (order_id);

alter table test add index (word_id);

show indexes from test;

+-------+-------------+--------------+-------------+-----------+-------------+----------+------------+

| Table | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Index_type |

+-------+-------------+--------------+-------------+-----------+-------------+----------+------------+

| test  | phrase_id   |            1 | phrase_id   | NULL      |           1 |     NULL | HASH       |

| test  | order_id    |            1 | order_id    | NULL      |           2 |     NULL | HASH       |

| test  | word_id     |            1 | word_id     | NULL      |           1 |     NULL | HASH       |

+-------+-------------+--------------+-------------+-----------+-------------+----------+------------+

explain select phrase_id, group_concat(

    word_id order by order_id asc

) as words_id from test group by phrase_id;

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using filesort |

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

FORCE/USE INDEX так же не приводит к использованию индексов...

Отредактированно FiMko (26.03.2010 23:28:12)

vasya · 27.03.2010 00:19:57

Потому что вы используете тип индекса HASH (по умолчанию для memory таблиц).
Используйте:

alter table test add index (phrase_id) USING BTREE;

FiMko · 27.03.2010 00:28:09

vasya написал:
Потому что вы используете тип индекса HASH (по умолчанию для memory таблиц).
Используйте:
alter table test add index (phrase_id) USING BTREE;

Спасибо! Теперь работает корректно:

explain select phrase_id, group_concat(

    word_id order by order_id asc

) as words_id from test group by phrase_id;

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+

| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra |

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+

|  1 | SIMPLE      | test  | index | NULL          | phrase_id | 4       | NULL |    4 |       |

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+

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

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html написал:
Hash indexes have somewhat different characteristics from those just discussed:

They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values.

The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)

MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM table to a hash-indexed MEMORY table.

Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)

Отредактированно FiMko (27.03.2010 00:28:26)

Форум пользователей MySQL

#1 24.03.2010 22:30:01

Вызов хранимой процедуры из другой хранимой процедуры

#2 24.03.2010 23:52:11

Re: Вызов хранимой процедуры из другой хранимой процедуры

#3 24.03.2010 23:55:06

Re: Вызов хранимой процедуры из другой хранимой процедуры

vasya написал:

#4 24.03.2010 23:58:09

Re: Вызов хранимой процедуры из другой хранимой процедуры

#5 25.03.2010 00:17:01

Re: Вызов хранимой процедуры из другой хранимой процедуры

vasya написал:

#6 25.03.2010 00:24:44

Re: Вызов хранимой процедуры из другой хранимой процедуры

#7 25.03.2010 00:31:59

Re: Вызов хранимой процедуры из другой хранимой процедуры

paulus написал:

paulus написал:

#8 25.03.2010 05:56:02

Re: Вызов хранимой процедуры из другой хранимой процедуры

#9 25.03.2010 06:04:36

Re: Вызов хранимой процедуры из другой хранимой процедуры

#10 25.03.2010 10:34:31

Re: Вызов хранимой процедуры из другой хранимой процедуры

LazY написал:

paulus написал:

paulus написал:

#11 26.03.2010 23:23:27

Re: Вызов хранимой процедуры из другой хранимой процедуры

LazY написал:

#12 27.03.2010 00:19:57

Re: Вызов хранимой процедуры из другой хранимой процедуры

#13 27.03.2010 00:28:09

Re: Вызов хранимой процедуры из другой хранимой процедуры

vasya написал:

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html написал:

Board footer