# Mysql Slow Select **URL:** \ **Category:** Archived Topics\ **Created:** [December 3, 2015, 10:06am UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866 "2015-12-03T10:06:34Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![MoisesZV](https://avatars.discourse-cdn.com/v4/letter/m/e68b1a/32.png) [@MoisesZV](https://forum.inductiveautomation.com/u/MoisesZV)\ **Post date:** [December 3, 2015, 10:06am UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/1 "2015-12-03T10:06:34Z")
Hi, I have a problem when I try to get data from a mysql db, I have this table : ```auto CREATE TABLE `events_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id_tag` int(11) NOT NULL, `value` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `date` (`date`) ) ENGINE=InnoDB AUTO_INCREMENT=1683945 DEFAULT CHARSET=latin1; ``` I have multiple tags with a value changed event, when this tags change the value, insert data to my table. In one hour I get 120.000 records, so when I try to get this data to a table component in igntion I get a query that last 10 seconds, is this normal for the for the large amount of data? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 3, 2015, 1:13pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/2 "2015-12-03T13:13:18Z")
You don’t say what indices you’ve created, nor shown the query. If the columns used in the where clause are not indexed, the DB must read every record to pick out the ones you want. ---
**Author:** ![MoisesZV](https://avatars.discourse-cdn.com/v4/letter/m/e68b1a/32.png) [@MoisesZV](https://forum.inductiveautomation.com/u/MoisesZV)\ **Post date:** [December 3, 2015, 2:35pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/3 "2015-12-03T14:35:40Z")
Sorry I’m new with databases configuration with large amount of data, I’m still learning how they work. I created an index for date. ```auto CREATE INDEX date ON events_table(date); ``` I use this query: ```auto select date,id_tag,value from events_table where date >= '2015-12-02 21:00:00' AND date <= '2015-12-02 23:00:00' 246855 row(s) returned 1.591 sec / 17.893 sec ``` That was just 2 hours, but my client probably want data from a week or more, what can I do with this? I was thinkin to use another DB like mssql, probably that works better. I have 2 weeks for resolve this. ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [December 3, 2015, 3:15pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/4 "2015-12-03T15:15:41Z")
Several things will affect the speed of this query. On the hardware side, databases want lots of memory and fast disk access. If you’re interested, I can go into the nitty gritty detail of how this query will work, but the bottom line is give the db every scrap of memory you can. This will be true for both MySQL and MS SQLServer. On the software side, if you have a lot of inserts, indexes can sometimes need to be rebuilt. I can’t remember if MySQL is supposed to rebuild automatically from time to time, but I know on some personal projects I’ve done a manual rebuild after inserting a quarter million records and had a drastic improvement in search times. Granted, my inserts were not in index order and yours probably are, but it’s something to try. There are definitely differences in how well different db’s optimize queries (and it’s part of what you pay for with the big name db’s), but this query is so simple I’m not seeing how switching from MySQL to SQLServer is going to get you a huge time improvement. I’d be looking at memory and disk access times first. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 3, 2015, 3:54pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/5 "2015-12-03T15:54:10Z")
Working with hundreds of thousands of rows prompted the creation of the [Time Series DB Cache](https://inductiveautomation.com/forum/viewtopic.php?f=50&t=14289) module. It breaks large queries like this into chunks, populating the client-side dataset as the data arrives. And then hangs onto it in a cache as long as it’s referenced in an expression. Plus a few minutes grace to allow a user to scroll back and forth quickly along a timeline. It hasn’t been tested with MySQL that I know of, but I’d be happy to iron out any wrinkles you find. ---
**Author:** ![MoisesZV](https://avatars.discourse-cdn.com/v4/letter/m/e68b1a/32.png) [@MoisesZV](https://forum.inductiveautomation.com/u/MoisesZV)\ **Post date:** [December 3, 2015, 8:56pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/6 "2015-12-03T20:56:59Z")
[quote=“pturmel”]Working with hundreds of thousands of rows prompted the creation of the [Time Series DB Cache](https://inductiveautomation.com/forum/viewtopic.php?f=50&t=14289) module. It breaks large queries like this into chunks, populating the client-side dataset as the data arrives. And then hangs onto it in a cache as long as it’s referenced in an expression. Plus a few minutes grace to allow a user to scroll back and forth quickly along a timeline. It hasn’t been tested with MySQL that I know of, but I’d be happy to iron out any wrinkles you find.[/quote] If this module solve my problem I would buy without thinking because I need this functionality. Now I’m trying to integrate this module but I can’t understand how it’s work. I created a new window with a table component and a button, In the manual I see this two scripting function ```auto system.db.registerSeriesCache system.db.getSeriesCache ``` Now I’m trying to figurate how use this to populate my table, the system.db.registerSeriesCache section say this: ```auto system.db.registerSeriesCache(datasourcename, tablename, timestampcolumn, whereclause) ``` I have to replace this parameters? I’m trying this: system.db.registerSeriesCache(db, events\_table, date, whereclause) Now with system.db.getSeriesCache system.db.getSeriesCache(handle, begints, endts, [gapTolerance,] valuecolumns[]) I have to replace valuecolumns? or I have to put my columns inside the []? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 4, 2015, 2:17am UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/7 "2015-12-04T02:17:51Z")
The script functions are offered for use in background threads and other long-running situations – you have to hold onto the ID from registerSeriesCache() to use in a loop with getSeriesCache(), then release it when you’re done. In components, you should bind the timeSeriesCache() expression function to the table’s dataset, or to a classic chart’s dataset. In your case, it would look something like:`timeSeriesCache('myDatasource', 'events_table', 'date', '', {Root Container.Date Range.startDate}, {Root Container.Date Range.endDate}, 'id_tag', 'value')`Where the time span to return comes from a Date Range component. Substitute as needed. Note that you must capitalize the column names exactly as defined in the database – the cache generates the queries with explicit quoting. ---
**Author:** ![MoisesZV](https://avatars.discourse-cdn.com/v4/letter/m/e68b1a/32.png) [@MoisesZV](https://forum.inductiveautomation.com/u/MoisesZV)\ **Post date:** [December 4, 2015, 1:41pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/8 "2015-12-04T13:41:20Z")
I do not know where to start, the manual show a list of function but there is not a step by step guide, or a video demostration. I created a custom property in my table, and I put timeSeriesCache expresion inside this property and I don’t get any data, I think because I don’t have data in cache. Now I’m trying to use “system.db.registerSeriesCache” to get cached data, I put it in a buton and I get the handle. ```auto handle=system.db.registerSeriesCache('DB', 'events_table','date',' ') event.source.parent.getComponent('Table').handle = handle ``` Now I don’t know if I have to use the expression “timeSeriesCache” or “system.db.getSeriesCache” in a button component. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 4, 2015, 3:11pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/9 "2015-12-04T15:11:50Z")
[quote=“MoisesZV”]I created a custom property in my table, and I put timeSeriesCache expresion inside this property and I don’t get any data, I think because I don’t have data in cache.[/quote]Please show the expression you used (paste it in a code block, please). You shouldn’t have needed a custom property. Put the expression on the ‘data’ property of your table. Make sure you provide all of the information the expression needs, as shown in the table [here](http://files.inductiveautomation.com/modulemarket/180/88/version/246/doc/expression.timeseriescache.html). The WHERE clause should be an empty string if you don’t need it. (Not even a space.) If you have syntax errors in your parameters, they’ll show up in the gateway console when it tries to run the query. The expression function takes all of the arguments and takes care of registering, getting, and releasing in a background thread for you.[quote=“MoisesZV”]Now I’m trying to use “system.db.registerSeriesCache” to get cached data, I put it in a buton and I get the handle.[/quote]Please don’t. The scripting functions are for use in background threads, not in components. Let the expression function do all the work. If you can’t get the caching to work with the expression function, it isn’t going to work with the scripting functions. {If you have strange polling requirements, the scripting might be useful later. I added the script functions to the module to allow me to run a keep-alive script for some critical data that isn’t always displayed.} ---
**Author:** ![MoisesZV](https://avatars.discourse-cdn.com/v4/letter/m/e68b1a/32.png) [@MoisesZV](https://forum.inductiveautomation.com/u/MoisesZV)\ **Post date:** [December 4, 2015, 3:50pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/10 "2015-12-04T15:50:59Z")
Thanks pturmel, now is working, but with some issues. When I query a large amount of data like a 1 million records, the table begin to show the data but in some point the CPU Usage is going to 100% then I can’t do anything, I have to force close ignition. AMD APU A6 6400K 3.9 GHz Dual Core 8gb Ram 1333mhz ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 4, 2015, 5:00pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/11 "2015-12-04T17:00:35Z")
Caching like this uses a lot of ram, especially on the client. Make sure you’ve allowed the client and the designer to do so. It’s a trade-off between speed/bandwidth and ram. Another possibility is that the either the chunk size (Bulk Limit) or the pace (Bulk Pace ms) is too aggressive for your hardware. You can set these on a cache-by-cache basis in the gateway configuration pages under “Time Series Caches”. ---
**Author:** ![MoisesZV](https://avatars.discourse-cdn.com/v4/letter/m/e68b1a/32.png) [@MoisesZV](https://forum.inductiveautomation.com/u/MoisesZV)\ **Post date:** [December 4, 2015, 7:01pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/12 "2015-12-04T19:01:38Z")
I’m trying with RT Pace ms 1000, Bulk Pace ms 1000 and Bulk Limit 2000, but still 100% cpu when it reaches more than 1 million records ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 4, 2015, 7:27pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/13 "2015-12-04T19:27:01Z")
Is it the gateway machine or the client machine @ 100% ? And does it recover if you leave it alone for 10-15 minutes? ---
**Author:** ![MoisesZV](https://avatars.discourse-cdn.com/v4/letter/m/e68b1a/32.png) [@MoisesZV](https://forum.inductiveautomation.com/u/MoisesZV)\ **Post date:** [December 4, 2015, 7:35pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/14 "2015-12-04T19:35:37Z")
I’m testing in the gateway machine, in the designer. Now I’m testing with RT Pace ms 1000, Bulk Pace ms 1000 and Bulk Limit 350, and the cpu is 50% and 60%. Edit: Now it’s freeze again. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 4, 2015, 8:55pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/15 "2015-12-04T20:55:32Z")
[quote=“MoisesZV”]I’m testing in the gateway machine, in the designer.[/quote]How much memory is each taking up? Also, how quickly do queries like the one originally given, with a limit clause of 1000, run outside of Ignition? Using MySQL Workbench or similar. The pace settings should be significantly larger than the worst case query response time for the chosen bulk limit.[quote=“MoisesZV”]Now I’m testing with RT Pace ms 1000, Bulk Pace ms 1000 and Bulk Limit 350, and the cpu is 50% and 60%.[/quote]That won’t help much. You can’t make progress querying millions of rows 350 at a time. In fact, the module ignores any Bulk limit less than 1000. Set both pace ms to 10000 for a bit to test with. It sounds like your test machine is also your database? And only has two cores & 8G ram? You might have unreasonable performance expectations for millions of rows 🙂[quote=“MoisesZV”]Edit: Now it’s freeze again.[/quote]I’m curious if you can connect to ignition, the database, or the OS from another machine while it appears to be frozen. Please turn on “Debug” logging for the AbstractCache class. Then send your logs to [support@automation-pros.com](mailto:support@automation-pros.com). ---
**Author:** ![Pat](https://avatars.discourse-cdn.com/v4/letter/p/8491ac/32.png) [@Pat](https://forum.inductiveautomation.com/u/Pat)\ **Post date:** [December 10, 2015, 6:26pm UTC](https://forum.inductiveautomation.com/t/mysql-slow-select/10866/16 "2015-12-10T18:26:52Z")
Although your example query only shows date in the Where clause, if you are querying based on date and id\_tag then you’ll want to create an Index on date and id\_tag. But, why not just use Ignition’s Historian? You can setup partitioning in the Historian, this would make smaller tables for which to query, hence speeding up your query.