CODEWITHSUNDEEP
mysqlsqlsql-view
MrTomAsh
MrTomAsh

Reputation: 174

SQL Views for single row (with where clause)

I'm working on a system that administrates courses, with multiple classes, with multiple lessons and multiple consumers on them. As the system grows more data were required so with some performance issues I've decided to go with SQL Views. We're using MySQL.

So I've replaced old calls to the DB (for example for the single lesson)

select * from `courses_classes_lessons` where `courses_classes_lessons`.`deleted_at` is null limit 1;
select count(consumer_id) as consumers_count from `courses_classes_lessons_consumers` where `lesson_id` = '448' limit 1;
select `max_consumers` from `courses_classes` where `id` = '65' limit 1;
select `id` from `courses_classes_lessons` left join `courses_classes_lessons_consumers` on `courses_classes_lessons_consumers`.`lesson_id` = `courses_classes_lessons`.`id` where `id` = '448' group by `courses_classes_lessons`.`id` having count(courses_classes_lessons_consumers.consumer_id) < '4' limit 1;
select courses_classes.max_consumers - LEAST(count(courses_classes_lessons_consumers.consumer_id), courses_classes.max_consumers) as available_spaces from `courses_classes_lessons` left join `courses_classes_lessons_consumers` on `courses_classes_lessons_consumers`.`lesson_id` = `courses_classes_lessons`.`id` left join `courses_classes` on `courses_classes_lessons`.`class_id` = `courses_classes`.`id` where `courses_classes_lessons`.`id` = '448' group by `courses_classes`.`id` limit 1;

The above took around 4-5ms

with the SQL View as follow:

CREATE OR REPLACE VIEW `courses_classes_lessons_view` AS
SELECT
    courses_classes_lessons.id AS lesson_id,
    (SELECT
            max_consumers
        FROM
            courses_classes
        WHERE
            id = courses_classes_lessons.class_id
        LIMIT 1) AS class_max_consumers,
    (SELECT
        count(consumer_id)
    FROM
        courses_classes_lessons_consumers
    WHERE
        lesson_id = courses_classes_lessons.id) AS consumers_count, 
    (SELECT
        CASE WHEN consumers_count >= class_max_consumers THEN
            TRUE
        ELSE
            FALSE
        END AS is_full) AS is_full, 
    (CASE WHEN courses_classes_lessons.completed_at > NOW() THEN
        'completed'
    WHEN courses_classes_lessons.cancelled_at > NOW() THEN
        'cancelled'
    WHEN courses_classes_lessons.starts_at > NOW() THEN
        'upcoming'
    ELSE
        'incomplete'
    END) AS status, 
    (SELECT
        class_max_consumers - LEAST(consumers_count, class_max_consumers)) AS available_spaces
    FROM
        courses_classes_lessons

The problem I'm having is that doesn't matter if I'm loading the whole View or a single row from it - it always takes about 6-9s to load! But when I've tried the same query with a WHERE clause it takes about 500μs. I'm new to SQL View and confused - why there are no indexes/primary keys that I could use to load a single row quickly? Am I doing something wrong?

EXPLAIN RESULT

INSERT INTO `courses_classes` (`id`, `select_type`, `table`, `partitions`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `filtered`, `Extra`) VALUES
(1, 'PRIMARY', 'courses_classes_lessons', NULL, 'ALL', NULL, NULL, NULL, NULL, 478832, 100.00, NULL),
(3, 'DEPENDENT SUBQUERY', 'courses_classes_lessons_consumers', NULL, 'ref', 'PRIMARY,courses_classes_lessons_consumers_lesson_id_index', 'courses_classes_lessons_consumers_lesson_id_index', '4', 'api.courses_classes_lessons.id', 3, 100.00, 'Using index'),
(2, 'DEPENDENT SUBQUERY', 'courses_classes', NULL, 'eq_ref', 'PRIMARY,courses_classes_id_parent_id_index', 'PRIMARY', '4', 'api.courses_classes_lessons.class_id', 1, 100.00, NULL);

TABLE STRUCTURE

Lessons

CREATE TABLE `courses_classes_lessons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `franchisee_id` int(10) unsigned NOT NULL,
  `class_id` int(10) unsigned NOT NULL,
  `instructor_id` int(10) unsigned NOT NULL,
  `instructor_rate` int(10) unsigned NOT NULL DEFAULT '0',
  `instructor_total` int(10) unsigned NOT NULL DEFAULT '0',
  `instructor_paid` tinyint(1) NOT NULL DEFAULT '0',
  `starts_at` timestamp NULL DEFAULT NULL,
  `ends_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `cancelled_reason` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cancelled_reason_extra` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `courses_classes_lessons_franchisee_id_foreign` (`franchisee_id`),
  KEY `courses_classes_lessons_class_id_foreign` (`class_id`),
  KEY `courses_classes_lessons_instructor_id_foreign` (`instructor_id`),
  KEY `courses_classes_lessons_starts_at_ends_at_index` (`starts_at`,`ends_at`),
  KEY `courses_classes_lessons_completed_at_index` (`completed_at`),
  KEY `courses_classes_lessons_cancelled_at_index` (`cancelled_at`),
  KEY `courses_classes_lessons_class_id_deleted_at_index` (`class_id`,`deleted_at`),
  KEY `courses_classes_lessons_deleted_at_index` (`deleted_at`),
  KEY `class_ownership_index` (`class_id`,`starts_at`,`cancelled_at`,`deleted_at`),
  CONSTRAINT `courses_classes_lessons_class_id_foreign` FOREIGN KEY (`class_id`) REFERENCES `courses_classes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `courses_classes_lessons_franchisee_id_foreign` FOREIGN KEY (`franchisee_id`) REFERENCES `franchisees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `courses_classes_lessons_instructor_id_foreign` FOREIGN KEY (`instructor_id`) REFERENCES `instructors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=487853 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Lessons consumers

CREATE TABLE `courses_classes_lessons_consumers` (
  `lesson_id` int(10) unsigned NOT NULL,
  `consumer_id` int(10) unsigned NOT NULL,
  `present` tinyint(1) DEFAULT NULL,
  `plan_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`lesson_id`,`consumer_id`),
  KEY `courses_classes_lessons_consumers_consumer_id_foreign` (`consumer_id`),
  KEY `courses_classes_lessons_consumers_plan_id_foreign` (`plan_id`),
  KEY `courses_classes_lessons_consumers_lesson_id_index` (`lesson_id`),
  KEY `courses_classes_lessons_consumers_present_index` (`present`),
  CONSTRAINT `courses_classes_lessons_consumers_consumer_id_foreign` FOREIGN KEY (`consumer_id`) REFERENCES `customers_consumers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `courses_classes_lessons_consumers_lesson_id_foreign` FOREIGN KEY (`lesson_id`) REFERENCES `courses_classes_lessons` (`id`) ON DELETE CASCADE,
  CONSTRAINT `courses_classes_lessons_consumers_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `customers_plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

From classes it's only using max_consumers int(10) unsigned NOT NULL DEFAULT '0',

UPDATE 1

I've changed SQL View to the following one:

CREATE OR REPLACE VIEW `courses_classes_lessons_view` AS
SELECT
    courses_classes_lessons.id AS lesson_id,
    courses_classes.max_consumers AS class_max_consumers,
    lessons_consumers.consumers_count AS consumers_count,
    (
        SELECT
            CASE WHEN consumers_count >= class_max_consumers THEN
                TRUE
            ELSE
                FALSE
            END AS is_full) AS is_full,
        (
            CASE WHEN courses_classes_lessons.completed_at > NOW() THEN
                'completed'
            WHEN courses_classes_lessons.cancelled_at > NOW() THEN
                'cancelled'
            WHEN courses_classes_lessons.starts_at > NOW() THEN
                'upcoming'
            ELSE
                'incomplete'
            END) AS status,
        (
            SELECT
                class_max_consumers - LEAST(consumers_count, class_max_consumers)) AS available_spaces
        FROM
            courses_classes_lessons
            JOIN courses_classes ON courses_classes.id = courses_classes_lessons.class_id
            JOIN (
                SELECT
                    lesson_id,
                    count(*) AS consumers_count
                FROM
                    courses_classes_lessons_consumers
                GROUP BY
                    courses_classes_lessons_consumers.lesson_id) AS lessons_consumers ON lessons_consumers.lesson_id = courses_classes_lessons.id;

and even though the SELECT query itself seems to be way slower than the previous one then as the View it seems to perform way better. It's still not as fast as I wish it will be but it's a step forward.

Overall improvement jumps from 6-7s to around 800ms, the aim here is in the area of 500μs-1ms. Any adivces how I can improve my SQL View more?

UPDATE 2

Ok, I've found the bottleneck! Again - it's kinda similar to the last one (SELECT query works fast for a single row, but SQL VIEW is trying to access the whole table at once every time.

My new lesson SQL VIEW:

CREATE OR REPLACE VIEW `courses_classes_lessons_view` AS
                SELECT
                courses_classes_lessons.id AS lesson_id,
                courses_classes.max_consumers AS class_max_consumers,
                IFNULL(lessons_consumers.consumers_count,0) AS consumers_count,
                (
                    SELECT
                        CASE WHEN consumers_count >= class_max_consumers THEN
                            TRUE
                        ELSE
                            FALSE
                        END AS is_full) AS is_full,
                    (
                        CASE WHEN courses_classes_lessons.completed_at > NOW() THEN
                            'completed'
                        WHEN courses_classes_lessons.cancelled_at > NOW() THEN
                            'cancelled'
                        WHEN courses_classes_lessons.starts_at > NOW() THEN
                            'upcoming'
                        ELSE
                            'incomplete'
                        END) AS status,
                    (
                        SELECT
                            IFNULL(class_max_consumers, 0) - LEAST(IFNULL(consumers_count,0), class_max_consumers)) AS available_spaces
                    FROM
                        courses_classes_lessons
                        JOIN courses_classes ON courses_classes.id = courses_classes_lessons.class_id
                        LEFT JOIN courses_classes_lessons_consumers_view AS lessons_consumers ON lessons_consumers.lesson_id = courses_classes_lessons.id;

Another SQL View - this time for consumers:

CREATE OR REPLACE VIEW `courses_classes_lessons_consumers_view` AS
                SELECT
                    lesson_id,
                    IFNULL(count(
                        consumer_id),0) AS consumers_count
                FROM
                    courses_classes_lessons_consumers
                GROUP BY
                    courses_classes_lessons_consumers.lesson_id;

And looks like this one is the trouble maker! The consumers table is above, and here is the explain for the above SELECT query:

INSERT INTO `courses_classes_lessons_consumers` (`id`, `select_type`, `table`, `partitions`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `filtered`, `Extra`)
        VALUES(1, 'SIMPLE', 'courses_classes_lessons_consumers', NULL, 'index', 'PRIMARY,courses_classes_lessons_consumers_consumer_id_foreign,courses_classes_lessons_consumers_plan_id_foreign,courses_classes_lessons_consumers_lesson_id_index,courses_classes_lessons_consumers_present_index', 'courses_classes_lessons_consumers_lesson_id_index', '4', NULL, 1330649, 100.00, 'Using index');

Any idea how to spread up this count?

Upvotes: 0

Views: 133

Answers (2)

MrTomAsh
MrTomAsh

Reputation: 174

After many attempts, it looks like that the Procedure way is the best approach and I won't be spending more time on the SQL Views

Here's the procedure I wrote:

CREATE PROCEDURE `LessonData`(
                IN lessonId INT(10)
                )
            BEGIN
                SELECT
                courses_classes_lessons.id AS lesson_id,
                courses_classes.max_consumers AS class_max_consumers,
                IFNULL((SELECT
                    count(consumer_id) as consumers_count
                FROM
                    courses_classes_lessons_consumers
                WHERE
                    lesson_id = courses_classes_lessons.id
                GROUP BY
                    courses_classes_lessons_consumers.lesson_id), 0) AS consumers_count,
                (
                    SELECT
                        CASE WHEN consumers_count >= class_max_consumers THEN
                            TRUE
                        ELSE
                            FALSE
                        END) AS is_full,
                    (
                        CASE WHEN courses_classes_lessons.completed_at > NOW() THEN
                            'completed'
                        WHEN courses_classes_lessons.cancelled_at > NOW() THEN
                            'cancelled'
                        WHEN courses_classes_lessons.starts_at > NOW() THEN
                            'upcoming'
                        ELSE
                            'incomplete'
                        END) AS status,
                    (
                        SELECT
                            class_max_consumers - LEAST(consumers_count, class_max_consumers)) AS available_spaces
                    FROM
                        courses_classes_lessons
                        JOIN courses_classes ON courses_classes.id = courses_classes_lessons.class_id
                    WHERE courses_classes_lessons.id = lessonId;    
            END

And the execution time for it is around 500μs-1ms.

Thank you all for your help!

Upvotes: 0

Rick James
Rick James

Reputation: 142298

Consider writing a Stored procedure; it may be able to get the 448 put into place to be better optimized.

  • If you know there will be only one row (such as when doing COUNT(*)), skip the LIMIT 1.
  • Unless consumer_id might be NULL, use COUNT(*) instead of COUNT(consumer_id).
  • A LIMIT without an ORDER BY leaves you getting a random row.
  • If courses_classes_lessons_consumers is a many-to-many mapping table, I will probably have some index advice after I see SHOW CREATE TABLE.
  • Which of the 5 SELECTs is the slowest?

Upvotes: 1

Related Questions