# Query running causing perfromance issue **URL:** \ **Category:** Archived Topics\ **Created:** [July 9, 2010, 2:12pm UTC](https://forum.inductiveautomation.com/t/query-running-causing-perfromance-issue/2020 "2010-07-09T14:12:02Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![rthomas](https://avatars.discourse-cdn.com/v4/letter/r/ad7895/32.png) [@rthomas](https://forum.inductiveautomation.com/u/rthomas)\ **Post date:** [July 9, 2010, 2:12pm UTC](https://forum.inductiveautomation.com/t/query-running-causing-perfromance-issue/2020/1 "2010-07-09T14:12:02Z")
In my slow query log i see the following query running very often causing performance hang in my factory PMI clients. I believe that it is an internal sql from either FPMI or FSQL, as i have no sql like this. SELECT A.`id` , A.`path` , A.`name` , A.`configchange` FROM sqlt\_core A, sqlt\_core B WHERE A.`id` != B.`id` AND A.`path` = B.`path` AND A.`name` = B.`name` AND A.`deleted` =0 [color=#0080BF]# Time: 100707 11:11:53 # User@Host: automation[automation] @ [172.20.251.18] # Query\_time: 26 Lock\_time: 0 Rows\_sent: 0 Rows\_examined: 2757566 SELECT A.`id`, A.`path`, A.`name`, A.`configchange` FROM sqlt\_core A, sqlt\_core B WHERE A.`id` != B.`id` AND A.`path`=B.`path` AND A.`n ame`=B.`name` AND A.`deleted`=0;[/color] Im my (MYSQL) system, we have 21,000+ tags and this query is running 30+ seconds and seems to be locking SQLT\_Core. can you assist me in determining where this query is coming from please ? ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [July 9, 2010, 3:18pm UTC](https://forum.inductiveautomation.com/t/query-running-causing-perfromance-issue/2020/2 "2010-07-09T15:18:08Z")
Check your sqlt\_core table to see if you have any indexes defined. That query is looking for duplicates. If you indexes are defined we can add one with the path and name to speed up that query. ---
**Author:** ![rthomas](https://avatars.discourse-cdn.com/v4/letter/r/ad7895/32.png) [@rthomas](https://forum.inductiveautomation.com/u/rthomas)\ **Post date:** [July 10, 2010, 6:28am UTC](https://forum.inductiveautomation.com/t/query-running-causing-perfromance-issue/2020/3 "2010-07-10T06:28:49Z")
Travis, That table has indexes already. it has 19,585 rows currently. Indexes: Documentation Keyname Type Unique Packed Field Cardinality Collation Null Comment PRIMARY BTREE Yes No id 19585 A sqlt\_core\_name\_ndx BTREE No No name 0 A YES sqlt\_core\_path\_ndx BTREE No No path 0 A YES sqlt\_core\_valuechange\_ndx BTREE No No valuechange 0 A YES sqlt\_core\_configchange\_ndx BTREE No No configchange 0 A YES Any other ideas ? ---
**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\ **Post date:** [July 11, 2010, 6:52am UTC](https://forum.inductiveautomation.com/t/query-running-causing-perfromance-issue/2020/4 "2010-07-11T06:52:08Z")
Post the results from `EXPLAIN SELECT A.`id` , A.`path` , A.`name` , A.`configchange` FROM sqlt_core A, sqlt_core B WHERE A.`id` != B.`id` AND A.`path` = B.`path` AND A.`name` = B.`name` AND A.`deleted` =0` ---
**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\ **Post date:** [July 11, 2010, 6:53am UTC](https://forum.inductiveautomation.com/t/query-running-causing-perfromance-issue/2020/5 "2010-07-11T06:53:25Z")
Also, you need an index on `deleted` ---
**Author:** ![rthomas](https://avatars.discourse-cdn.com/v4/letter/r/ad7895/32.png) [@rthomas](https://forum.inductiveautomation.com/u/rthomas)\ **Post date:** [July 12, 2010, 3:41am UTC](https://forum.inductiveautomation.com/t/query-running-causing-perfromance-issue/2020/6 "2010-07-12T03:41:37Z")
After the index on deleted… SQL result id select\_type table type possible\_keys key key\_len ref rows Extra 1 SIMPLE A ALL sqlt\_core\_name\_ndx,sqlt\_core\_path\_ndx,deleted NULL NULL NULL 14689 Using where 1 SIMPLE B ref sqlt\_core\_name\_ndx,sqlt\_core\_path\_ndx sqlt\_core\_name\_ndx 258 fsql.A.name 3 Using where Thank you. ---
**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\ **Post date:** [July 13, 2010, 11:59pm UTC](https://forum.inductiveautomation.com/t/query-running-causing-perfromance-issue/2020/7 "2010-07-13T23:59:51Z")
`EXPLAIN SELECT A.`id` , A.`path` , A.`name` , A.`configchange` FROM sqlt_core A, sqlt_core B WHERE A.`id` != B.`id` AND A.`path` = B.`path` AND A.`name` = B.`name` AND A.`deleted` =0` You need to index everything that is used in the where or join commands. In this case, make sure id, path, name and deleted are indexed. looks like `id` needs an index as well. ---
**Author:** ![rthomas](https://avatars.discourse-cdn.com/v4/letter/r/ad7895/32.png) [@rthomas](https://forum.inductiveautomation.com/u/rthomas)\ **Post date:** [July 16, 2010, 3:12am UTC](https://forum.inductiveautomation.com/t/query-running-causing-perfromance-issue/2020/8 "2010-07-16T03:12:48Z")
All four fields are indexed: Indexes: Documentation Keyname Type Unique Packed Field Cardinality Collation Null Comment PRIMARY BTREE Yes No id 19766 A sqlt\_core\_name\_ndx BTREE No No name 6588 A YES sqlt\_core\_path\_ndx BTREE No No path 732 A YES sqlt\_core\_valuechange\_ndx BTREE No No valuechange 705 A YES sqlt\_core\_configchange\_ndx BTREE No No configchange 681 A YES deleted\_ndx BTREE No No deleted 1 A YES ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [July 20, 2010, 5:13pm UTC](https://forum.inductiveautomation.com/t/query-running-causing-perfromance-issue/2020/9 "2010-07-20T17:13:14Z")
For what its worth, this query is run by FactoryPMI (to look for duplicates) when the SQLTags system starts up. So under normal running conditions, it won’t run, and won’t be a problem. If it seems to be running more frequently, it is probably because your datasource connection is faulting out (for other reasons). So from this perspective you’re really troubleshooting the symptom of a different problem. I suggest you look into why your datasource connection is periodically faulting out.