Query execution time is twice that of php

[Orel-A]

Hi, I'm running an identical sql query in php and with nodejs & mariadb, on the same machine, on nodejs the query takes 90ms and on php it takes 45ms. I don't know why there is such a difference...? Any help is appreciated.

[rusher]

Could you share the query ? (after having created a benchmark to compare driver with different language - but not php- i've seen that even if our driver is more performant compare to mysql/mysql2 driver, there is still work to do. That will be in a 3.1 version)

If i can make a guess, i imagine that query is some SELECT with multiple fields/rows (default result format, creating multiple json objects for multiple rows/fields is quite expensive)

If so i would recommand trying option rowsAsArray. results are then not in json format like

[
    { id: 1, name: 'sea lions' }, 
    { id: 2, name: 'bird' }
]

but with value only :

[ 
    [ 1, 'sea lions' ], 
    [ 2, 'bird' ],
]

example const res = await connection.query({ rowsAsArray: true, sql: 'select * from animals' });

[Orel-A]

Thanks for the quick response. Here is my query:

SELECT q.*, o.title, COUNT(a.rid) AS questionResponses, SUM(q.optionid=a.optionid) AS optionAnswers FROM survey_quotas q
INNER JOIN question_options o ON o.optionid=q.optionid
LEFT JOIN survey_responses r ON r.sid=q.sid AND (r.status IN ('Complete', 'Disqualified') OR r.status='Partial' AND r.submitDate >= NOW() - INTERVAL 10 MINUTE)
LEFT JOIN survey_answers a ON a.rid=r.rid AND a.qid=q.qid
WHERE q.sid=46
GROUP BY q.qid, q.optionid

Edit: rowsAsArray didn't help, it didn't change the time taken. seems like a different problem

[rusher]

MySQLi or PDO_MySQL ? Do you use basic query or prepare + execute ?

[Orel-A]

basic query with MySQLi driver, PhpMyAdmin also gives the same 45ms time Edit: HeidiSQL software also gives 0.047 sec

[Orel-A]

Is there anything to test here? Maybe I'll provide you with some data to test on, but privately?

[rusher]

How many rows return the query ?

I don't see any immediate reason to why so much difference. HeidiSQL use underlying mariadb c/c connector. Performance using node.js with current develop branch (futur 3.1) is around 85% compare to c/c (c language). (but node.js has some other advantages).

It would be nice to have the DDL of the query, in order to reproduced that and see what's wrong there. If possible could you create one ticket in https://jira.mariadb.org/projects/CONJS/ describing the query ?

[Orel-A]

17 rows are returned, but not much data is passed. What is DDL of the query ? I don't know how to navigate within the url you've provided. How can I pass you the db with some data?

[rusher]

DDL = structure of your tables so, this can be done with SHOW CREATE TABLE survey_quotas, SHOW CREATE TABLE question_options, ...

[Orel-A]

sent you the db, you can remove that

[Orel-A]

did you manage to reproduce the same problem on your machine ?

[rusher]

hmm, i've tryed and the results with c, java and node.js i've 35-36ms for all languages.

    console.time("execution_time");
    let rows = await shareConn.query('SELECT q.*, o.title, COUNT(a.rid) AS questionResponses, SUM(q.optionid=a.optionid) AS optionAnswers FROM survey_quotas q\n' +
        '  INNER JOIN question_options o ON o.optionid=q.optionid\n' +
        '  LEFT JOIN survey_responses r ON r.sid=q.sid AND (r.status IN (\'Complete\', \'Disqualified\') OR r.status=\'Partial\' AND r.submitDate >= NOW() - INTERVAL 10 MINUTE)\n' +
        '  LEFT JOIN survey_answers a ON a.rid=r.rid AND a.qid=q.qid\n' +
        '  WHERE q.sid=46\n' +
        '  GROUP BY q.qid, q.optionid');
    console.timeEnd("execution_time");

[rusher]

maybe your code is different ?! Check your version in package-lock.json as well (i use mariadb 3.0.2)

[Orel-A]

you may be right. I'm currently trying to strip down my nodejs code. I'm using the queries within an application, obviously with other code but the measuring code is simply async await with console.time() so I thought nothing was wrong there. I'll report back if I find the cause

[Orel-A]

If I run the query with everything stripped, then yes I get something similar to 46ms. but when I use the code within fastify I get 90ms. and when it's in expressJs it's 100ms. so I guess it's the event loop that slows everything down ? Is there such a thing ? Or am I completly clueless here?

[Orel-A]

@rusher I think I got the problem. When I pass sid as string I get the high time, when I pass it as number it's quick Try this:

await db.query(`SELECT q.*, o.title, COUNT(a.rid) AS questionResponses, SUM(q.optionid=a.optionid) AS optionAnswers FROM survey_quotas q
INNER JOIN question_options o ON o.optionid=q.optionid
LEFT JOIN survey_responses r ON r.sid=q.sid AND (r.status IN ('Complete', 'Disqualified') OR r.status='Partial' AND r.submitDate >= NOW() - INTERVAL 10 MINUTE)
LEFT JOIN survey_answers a ON a.rid=r.rid AND a.qid=q.qid
WHERE q.sid=?
GROUP BY q.qid, q.optionid`, "46");

Why it adds 45ms to just parse a string???

[rusher]

I don't really know, but that's not a connector issue :

SELECT q.*, o.title, COUNT(a.rid) AS questionResponses, SUM(q.optionid=a.optionid) AS 
...
q.sid=46
...

=>36ms

SELECT q.*, o.title, COUNT(a.rid) AS questionResponses, SUM(q.optionid=a.optionid) AS 
...
q.sid='46'
...

=>66ms try with heidi, not to set 46, but '46' and you will have the same result in terms of performance.

[Orel-A]

confirmed, it's executing the query twice ?

[Orel-A]

I just checked with MySql 7.4, it doesn't have this disparity. The execution time is the same for both queries => 67ms.