Filter with all issue. Very slow

[drewhamlett]

Hi I have a table with quite a few rows(14,000,000). I recently found patio and it looks very promising. I did some basic queries on our user database and it seemed fine. When I started doing queries on a very large table something seemed very wrong.

    PkgLoad.filter({
                time_stamp: {
                    between: ['2011-01-01 00:00:00', '2011-01-01 0:3:59']
                }
            }).all(function(row) {

            });

This is the query I'm doing. I've checked what is being sent to mysql, and I'm logging all queries. This query only returns 6 rows from this table. If I copy the query that this function is creating, and run it, it completes instantly. It seems to take around 15 seconds to do this query through patio.

I've also tried using first where it uses the LIMIT 1 statement. It still takes forever.

This is the query it produces

SELECT * FROM `pkg_load` WHERE ((`time_stamp` >= '2011-01-01 00:00:00') AND (`time_stamp` <= '2011-01-01 0:4:59')) 

If I do a more simple query such as a findById(where I'm returning one result) it works great. I just wanted to make sure I'm not doing anything wrong. Thanks.

[drewhamlett]

I've actually tracked this down to the mysql nodejs driver that is used.

https://github.com/felixge/node-mysql

I've run tests and I cannot figure out why this is happening. The query is 10 to 15 times slower then the query I run myself on the database. As a test I did the same query in Java with Mysql JDBC driver. I'm not running into any issues with the JDBC driver.

[doug-martin]

I've never run into an issue like that before.

Is there anyway you can send me some mock data or table structure or some thing that I can try to test locally to see what might be causing this?

[drewhamlett]

Hi Doug, thanks for your interest in this. I've posted this issue in the node-mysql project.

Here is the issue.

mysqljs/mysql#200

If you want to look at it that would be great or you could close this one. The table structure is in that issue. Thanks for your help.

[doug-martin]

Issue opened on node-mysql 200 will reponed if needed

[drewhamlett]

Hi Doug this was fixed in the 2.0 version of node-mysql.

mysqljs/mysql#200