Level 73
What you need to do is analyze the SQL query that you run, and see if there is something in it that can be improved. So if you can get the query that you run in plain SQL and then run an explain plan on it, to see what takes time.
Jul 30, 2022
17
Level 2
Ajax loading taking too much time on datatable
I am using codeigniter 3 and enabled one data table for users list ,simple stuff done here . Yes i know that this is not in laravel or any relevant platform specific problem but i think this problem is not framework specific instead it is general mysql related problem and it can be observed in any framework so hoping for help here
There is one data table which fetches data on page load using ajax
- ajax hits my database and fetches data and count
- count is used for pagination
- data is used for rendering data
my model query looks like this
public function fetchAllClient($currentAdminId = null,$filter)
{
$this->db->select($this->getString)->from('rvd_client as c')
->join('rvd_client_details as cd', 'c.id_client = cd.id_client', 'inner')
->join('rvd_addresses as a', 'a.id_client = c.id_client', 'inner');
$this->searchForDataTable($filter);
$countQuery = "SELECT COUNT(*) AS total FROM rvd_client as c INNER JOIN
rvd_client_details as cd ON c.id_client = cd.id_client
INNER JOIN rvd_addresses as a ON a.id_client = c.id_client
WHERE {$this->getSearchString($filter)}";
if($currentAdminId != null ) {
$this->db->where('cd.assigned_user',(int)$_SESSION['logged_in']['id']);
$countQuery .= ' AND cd.assigned_user = '.$_SESSION['logged_in']['id'];
}
$countQuery .= ';';
$count = $this->db->query($countQuery);
$this->db->limit($filter['length'],$filter['start']);
$query = $this->db->get();
return [
'data' => $query->result_array(),
// 'total' => 8000,
'total' => $count->result_array()[0]['total']
];
}
private function getSearchString($filter)
{
return "(
unique_identity = '{$filter['search']['value']}'
OR firstname LIKE '%{$filter['search']['value']}%'
OR lastname LIKE '%{$filter['search']['value']}%'
OR CONCAT(firstName,' ',lastName) LIKE '%{$filter['search']['value']}%'
OR father_name LIKE '%{$filter['search']['value']}%'
OR mother_name LIKE '%{$filter['search']['value']}%'
OR mobile LIKE '%{$filter['search']['value']}%'
OR cast LIKE '%{$filter['search']['value']}%'
OR place_of_birth LIKE '%{$filter['search']['value']}%'
OR marital_status LIKE '%{$filter['search']['value']}%'
OR height LIKE '%{$filter['search']['value']}%'
OR category LIKE '%{$filter['search']['value']}%'
OR education LIKE '%{$filter['search']['value']}%'
OR gotra LIKE '%{$filter['search']['value']}%'
OR budget LIKE '%{$filter['search']['value']}%'
OR city LIKE '%{$filter['search']['value']}%'
OR state LIKE '%{$filter['search']['value']}%'
)";
}
private function searchForDataTable($filter)
{
$this->db->group_start()
->where("`unique_identity`", $filter['search']['value'])
->or_like("firstname", $filter['search']['value'])
->or_like("lastname", $filter['search']['value'])
->or_like("CONCAT(firstName,' ',lastName)", $filter['search']['value'])
->or_like("father_name", $filter['search']['value'])
->or_like("mother_name", $filter['search']['value'])
->or_like("mobile", $filter['search']['value'])
->or_like("cast", $filter['search']['value'])
->or_like("place_of_birth", $filter['search']['value'])
->or_like("marital_status", $filter['search']['value'])
->or_like("height", $filter['search']['value'])
->or_like("category", $filter['search']['value'])
->or_like("education", $filter['search']['value'])
->or_like("gotra", $filter['search']['value'])
->or_like("budget", $filter['search']['value'])
->or_like("city", $filter['search']['value'])
->or_like("state", $filter['search']['value'])
->group_end();
}
now the problem is on my function i am doing two things one is data fetching one is total count
and when this function is called it takes too much time
as we can see it took about 6 seconds which is too much as there is only 8k data right now in my table
Now the problem is count query as it is used for searching purpose too but it is requirement and thats why it is done as datatable can be used for searching too
when i comment my count query
now i am definitely sure that my count query is wrong somewhere but i am not able to solve this issue
i also took help for indexing
CREATE TABLE `rvd_client_details` (
`id` int(6) UNSIGNED NOT NULL,
`unique_identity` varchar(20) DEFAULT NULL,
`id_client` int(11) DEFAULT NULL,
`address_id` tinyint(4) DEFAULT NULL,
`firstname` varchar(40) DEFAULT NULL,
`lastname` varchar(30) DEFAULT NULL,
`occupation` text,
`father_name` varchar(100) DEFAULT NULL,
`father_occupation` text,
`work_experience` text,
`firm` text,
`hobbies` text,
`mother_name` varchar(100) DEFAULT NULL,
`mother_occupation` text,
`mobile` varchar(15) DEFAULT NULL,
`cast` varchar(30) DEFAULT NULL,
`gender` varchar(10) DEFAULT NULL,
`profile_img` varchar(255) DEFAULT NULL,
`other_images` mediumtext,
`assigned_user` text,
`height` varchar(15) DEFAULT NULL,
`date_of_birth` date DEFAULT NULL,
`time_of_birth` varchar(55) DEFAULT NULL,
`place_of_birth` varchar(30) DEFAULT NULL,
`complexion` varchar(255) DEFAULT NULL,
`native_place` varchar(255) DEFAULT NULL,
`religion` varchar(255) DEFAULT NULL,
`marital_status` varchar(15) DEFAULT NULL,
`category` varchar(30) DEFAULT NULL,
`education` mediumtext,
`gotra` varchar(40) DEFAULT NULL,
`business_details` text,
`budget` varchar(20) DEFAULT NULL,
`parental_side` mediumtext,
`maternal_side` mediumtext,
`siblings` mediumtext,
`family_mobile_number` text,
`modified_resume` varchar(255) DEFAULT NULL,
`original_resume` varchar(255) DEFAULT NULL,
`about` text,
`remark` text,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` varchar(255) DEFAULT '0000-00-00 00:00:00',
`assign_employee_date` varchar(255) NOT NULL DEFAULT '0000-00-00 00:00:00',
`created_by_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `rvd_client_details`
--
ALTER TABLE `rvd_client_details`
ADD PRIMARY KEY (`id`),
ADD KEY `unique_identity` (`unique_identity`,`firstname`,`lastname`,`father_name`,`mother_name`,`mobile`,`cast`,`height`,`place_of_birth`,`marital_status`,`category`,`gotra`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `rvd_client_details`
--
ALTER TABLE `rvd_client_details`
MODIFY `id` int(6) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;
this is my table schema so if any help can be given it would be very helpful
plain sql query
SELECT COUNT(*) AS total FROM rvd_client as c INNER JOIN
rvd_client_details as cd ON c.id_client = cd.id_client
INNER JOIN rvd_addresses as a ON a.id_client = c.id_client
WHERE (
unique_identity = ''
OR firstname LIKE '%%'
OR lastname LIKE '%%'
OR CONCAT(firstName,' ',lastName) LIKE '%%'
OR father_name LIKE '%%'
OR mother_name LIKE '%%'
OR mobile LIKE '%%'
OR cast LIKE '%%'
OR place_of_birth LIKE '%%'
OR marital_status LIKE '%%'
OR height LIKE '%%'
OR category LIKE '%%'
OR education LIKE '%%'
OR gotra LIKE '%%'
OR budget LIKE '%%'
OR city LIKE '%%'
OR state LIKE '%%'
);
and this is my sql explaination
Thanks
Level 2
@Tray2 okay let me update plain query too
Level 122
@rohansinghrawat you need to take that plain query, paste it into a sql tool and add EXPLAIN to the front of it
then when you run the query it will show you where mysql is spending its time and what indexes might be required
Level 2
@Snapey sir I am not so great in mysql so if i show that explain returned from mysql here can you help me with it ?
Level 122
@rohansinghrawat good time for you to learn. You are working with database, it needs to be part of your skillset
Level 122
@tray2 is much better than me on this, but the first thing that strikes me is that you do not have any indexes on the client_id columns in each table which are being used to join the tables
Level 2
@Snapey okay so i have to create index on each table for those right?
Level 102
I assume the codeigniter community has some way of getting each query run along with they timing? Laravel has debugbar (or clockwork or Ray)
Level 2
@Tray2 i have updated my question with query
Level 102
@rohansinghrawat I would imagine all of those like queries would slow it down by a lot. If you remove them, is it fast?
Level 2
@Sinnbeck yes i tried removing those like and it somehow helped me but as per my client's requirement he wanted to make a search in which he can search from all those columns and that was the only solution i got
I also tried indexing
Level 102
@rohansinghrawat maybe full text search indexing? Found an answer that is close to yours https://stackoverflow.com/a/67493312
Level 2
@Sinnbeck okay thanks i'll try to figure it out some solution
Level 73
@rohansinghrawat I agree with @sinnbeck, since you are using LIKE and they are almost always slowish you should consider using a full text index.
Result of the explain tells you that you probably need an index in the unique_id. Since you are using count, you really can't speed that up any since you need to count each row in the result, the trick is to limit that result.
Level 2
@Tray2 sir can you please give some query examples in support of your
the trick is to limit that result.
this quote
Level 73
@rohansinghrawat What I mean is that you want to have limited the number of rows that you do the count on.
For example,
SELECT COUNT(*) FROM table_one WHERE some_foreign_key = 1;
Will most likely need a foreign key index on the some_foreign_key column to be sped up.
Another way to write that query might be
SELECT
COUNT(t.id)
FROM
(
SELECT
id
FROM
table_one
WHERE
some_foreign_key = 1
) AS t;
I'm not saying that the second way of counting the records is faster in this case, but it might be in your query.
If you perform a heavy query to calculate something you want to do that as few times as possible,
You also need to use the proper indexes to speed everything up.
Level 2
@Tray2 okay I do have to study more on this topic
1 like
Please or to participate in this conversation.