102 lines
5.5 KiB
SQL
102 lines
5.5 KiB
SQL
-- Индексы для оптимизации запросов
|
|
-- PostgreSQL
|
|
--
|
|
-- ВАЖНО: Этот файл должен выполняться ПОСЛЕ schema.sql
|
|
-- Сначала создайте структуру базы данных (schema.sql), затем создайте индексы (indexes.sql)
|
|
|
|
-- Индексы для таблицы resources
|
|
CREATE INDEX IF NOT EXISTS idx_resources_slug ON resources(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_resources_is_active ON resources(is_active);
|
|
|
|
-- Индексы для таблицы actions
|
|
CREATE INDEX IF NOT EXISTS idx_actions_slug ON actions(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_actions_is_active ON actions(is_active);
|
|
|
|
-- Индексы для таблицы permissions
|
|
CREATE INDEX IF NOT EXISTS idx_permissions_slug ON permissions(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_permissions_resource_id ON permissions(resource_id);
|
|
CREATE INDEX IF NOT EXISTS idx_permissions_action_id ON permissions(action_id);
|
|
CREATE INDEX IF NOT EXISTS idx_permissions_resource_action ON permissions(resource_id, action_id);
|
|
CREATE INDEX IF NOT EXISTS idx_permissions_is_active ON permissions(is_active);
|
|
|
|
-- Индексы для таблицы roles
|
|
CREATE INDEX IF NOT EXISTS idx_roles_slug ON roles(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_roles_is_active ON roles(is_active);
|
|
|
|
-- Индексы для таблицы role_permissions
|
|
CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id ON role_permissions(role_id);
|
|
CREATE INDEX IF NOT EXISTS idx_role_permissions_permission_id ON role_permissions(permission_id);
|
|
|
|
-- Индексы для таблицы users
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_telegram_id ON users(telegram_id);
|
|
CREATE INDEX IF NOT EXISTS idx_users_telegram_user ON users(telegram_user);
|
|
CREATE INDEX IF NOT EXISTS idx_users_role_id ON users(role_id);
|
|
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);
|
|
|
|
-- Индексы для таблицы categories
|
|
CREATE INDEX IF NOT EXISTS idx_categories_parent_id ON categories(parent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_categories_slug ON categories(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_categories_is_active ON categories(is_active);
|
|
|
|
-- Индексы для таблицы products
|
|
CREATE INDEX IF NOT EXISTS idx_products_category_id ON products(category_id);
|
|
CREATE INDEX IF NOT EXISTS idx_products_slug ON products(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_products_sku ON products(sku);
|
|
CREATE INDEX IF NOT EXISTS idx_products_is_active ON products(is_active);
|
|
CREATE INDEX IF NOT EXISTS idx_products_is_featured ON products(is_featured);
|
|
CREATE INDEX IF NOT EXISTS idx_products_price ON products(price);
|
|
CREATE INDEX IF NOT EXISTS idx_products_discount ON products(discount_percent) WHERE discount_percent > 0;
|
|
|
|
-- Индексы для таблицы product_images
|
|
CREATE INDEX IF NOT EXISTS idx_product_images_product_id ON product_images(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_product_images_is_primary ON product_images(is_primary);
|
|
|
|
-- Индексы для таблицы addresses
|
|
CREATE INDEX IF NOT EXISTS idx_addresses_user_id ON addresses(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_addresses_is_default ON addresses(is_default);
|
|
|
|
-- Индексы для таблицы order_statuses
|
|
CREATE INDEX IF NOT EXISTS idx_order_statuses_slug ON order_statuses(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_order_statuses_is_active ON order_statuses(is_active);
|
|
|
|
-- Индексы для таблицы payment_methods
|
|
CREATE INDEX IF NOT EXISTS idx_payment_methods_slug ON payment_methods(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_payment_methods_is_active ON payment_methods(is_active);
|
|
|
|
-- Индексы для таблицы payment_statuses
|
|
CREATE INDEX IF NOT EXISTS idx_payment_statuses_slug ON payment_statuses(slug);
|
|
CREATE INDEX IF NOT EXISTS idx_payment_statuses_is_active ON payment_statuses(is_active);
|
|
|
|
-- Индексы для таблицы orders
|
|
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_orders_order_number ON orders(order_number);
|
|
CREATE INDEX IF NOT EXISTS idx_orders_status_id ON orders(status_id);
|
|
CREATE INDEX IF NOT EXISTS idx_orders_payment_method_id ON orders(payment_method_id);
|
|
CREATE INDEX IF NOT EXISTS idx_orders_payment_status_id ON orders(payment_status_id);
|
|
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);
|
|
|
|
-- Индексы для таблицы order_items
|
|
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_order_items_product_id ON order_items(product_id);
|
|
|
|
-- Индексы для таблицы cart_items
|
|
CREATE INDEX IF NOT EXISTS idx_cart_items_user_id ON cart_items(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_cart_items_product_id ON cart_items(product_id);
|
|
|
|
-- Индексы для таблицы reviews
|
|
CREATE INDEX IF NOT EXISTS idx_reviews_product_id ON reviews(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reviews_user_id ON reviews(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reviews_is_approved ON reviews(is_approved);
|
|
CREATE INDEX IF NOT EXISTS idx_reviews_rating ON reviews(rating);
|
|
|
|
-- Индексы для таблицы payments
|
|
CREATE INDEX IF NOT EXISTS idx_payments_order_id ON payments(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payments_payment_method_id ON payments(payment_method_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payments_status_id ON payments(status_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payments_transaction_id ON payments(transaction_id);
|
|
|
|
-- Индексы для таблицы settings
|
|
CREATE INDEX IF NOT EXISTS idx_settings_key ON settings(key);
|
|
|