I am facing issue of very slow result.
I am sharing table structure as and results also.
if you have any suggestion please update soon if possible for you.
================================================================= Table Structure --
https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Mar 25, 2019 at 11:48 AM -- Server version: 5.5.61-cll -- PHP Version: 7.2.7
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
--
-- Database: `energe3c_lms`
--
-- --------------------------------------------------------
--
-- Table structure for table `user_material`
--
CREATE TABLE `user_material` (
`id` int(11) NOT NULL,
`user_course_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`material_id` int(11) NOT NULL,
`attempt` int(11) NOT NULL,
`another_attempt` tinyint(1) DEFAULT '0',
`status` varchar(255) DEFAULT NULL,
`complete` tinyint(1) DEFAULT NULL,
`percent` float DEFAULT '0',
`time` varchar(255) DEFAULT NULL,
`marking_time` varchar(255) DEFAULT NULL,
`marked_by` int(11) DEFAULT NULL,
`feedback` text,
`submitted_date` datetime DEFAULT NULL,
`marking_date` datetime DEFAULT NULL,
`created` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `user_material`
--
ALTER TABLE `user_material`
ADD PRIMARY KEY (`id`),
ADD KEY `user_material-user` (`user_id`),
ADD KEY `user_material-material` (`material_id`),
ADD KEY `user_material-marking-user` (`marked_by`),
ADD KEY `user_course-user_material` (`user_course_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `user_material`
--
ALTER TABLE `user_material`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `user_material`
--
ALTER TABLE `user_material`
ADD CONSTRAINT `user_course-user_material` FOREIGN KEY (`user_course_id`) REFERENCES `user_course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `user_material-marking-user` FOREIGN KEY (`marked_by`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `user_material-material` FOREIGN KEY (`material_id`) REFERENCES `material` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `user_material-user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;
Showing rows 0 - 24 (25586268 total, Query took 0.0007 seconds.)
SELECT * FROM user_material
=================================================================
Showing rows 0 - 24 (77 total, Query took 22.9434 seconds.)
SELECT * FROM user_material where status='submitted'
=================================================================
Showing rows 0 - 24 (34026300 total, Query took 24.4978 seconds.) [submitted_date: ... - ...]
SELECT * FROM user_material ORDER BY submitted_date ASC
=================================================================
If You have any suggestion, please update me.
I am using this query in yii2 framework
JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)
