AJAX request takes too long to complete the request

Question

I have a MySQL query which gets 17000 rows. If i execute that query directly in MySQL terminal using Putty, it takes 17 ~ 20 sec to get all rows. This is fine.

But when i tried to execute the query using PHP with an AJAX request, It takes 50 ~ 60 sec to complete the request, even this query was cached.

I am using CodeIgniter PHP framework for caching and its AJAX request.

I agree, the HTTP request and PHP execution might take time when compared to direct MySQL query execution in the terminal.

MySQL direct query execution : 20 sec Using HTTP, php, Codeigniter and JSON format and it is cached : 60 sec.

The time difference too long i guess.

Model query that i am trying to execute:

mysql> explain SELECT col1, col2, col3, col4, IFNULL(col5,'N/A') AS 'col55', `col6`, col7, col8, col9 FROM table1 LEFT JOIN table1 ON table1.fkid_colid=`voyage id` AND table1.alias_type='voyage_display' WHERE column10='voyage';
+----+-------------+--------------+------+-------------------------------------+---------+---------+------------------------------------+-------+-------------+
| id | select_type | table        | type | possible_keys                       | key     | key_len | ref                                | rows  | Extra       |
+----+-------------+--------------+------+-------------------------------------+---------+---------+------------------------------------+-------+-------------+
|  1 | SIMPLE      | table1       | ALL  | NULL                                | NULL    | NULL    | NULL                               | 37770 | Using where |
|  1 | SIMPLE      | table1       | ref  | PRIMARY,fk_table1_colid_idx         | PRIMARY | 4       | database.table1.column ID |     1 | Using index |
+----+-------------+--------------+------+-------------------------------------+---------+---------+------------------------------------+-------+-------------+
2 rows in set (0.00 sec)

Have any idea for this longer loading time? it would be if you suggest any optimization techniques for this?

Answer 1

Yes. I just found the issue by profiling the controller function.

So as per the Codeigniter profiling the result as below,

Controller Execution Time : 27.7074 seconds.

And after that i looked Chrome console for the time taken to complete the request, From that i came to know one issue.

Stalled : 17.32 s
DNS Lookup: 1.000 ms
Initial connection  :262.000 ms
Request/Response        TIME
Request sent    0
Waiting (TTFB)  1.03 s
Content Download    29.84 s
Total time: 48.46 s

From the report, the big issue is "Stalled" time. It took 17.32 seconds. It was happened due to my side proxy issue and It cannot be avoid and it won't happen in my customer side. So it is negligible.

So,

Direct query execution 17 ~ 20 seconds

Ajax request for content download in browser side: 29 ~ 30 seconds.

So the difference is 10 seconds for 3MB content download and my customer is accepted :)

Thank you for your responses.