HI,
Have you tried to increase index inlineSize? It is 10 bytes by default.
Your indices uses simple value types (Java primitives) and all columns can
be easily inlined.
It should be enough to increase inlineSize up to 32 bytes (3 longs + 1 int
= 3*(8 /*long*/ + 1/*type code*/) + (4/*int*/ + 1/*type code*/)) to inline
all columns for the idx1, and up to 27 (3 longs) for idx2.
You can try to benchmark queries with different inline sizes to find
optimal ratio between speedup and index size.
On Mon, Sep 3, 2018 at 5:12 PM Prasad Bhalerao <prasadbhalerao1...@gmail.com>
wrote:
> Hi,
> My cache has 1 million rows and the sql is as follows.
> This sql is taking around 1.836 seconds to execute and this time increases
> as I go on adding the data to this cache. Some time it takes more than 4
> seconds.
>
> Is there any way to improve the execution time?
>
> *SQL:*
> SELECT id, moduleId,ipEnd, ipStart
> FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
> WHERE subscriptionId = ? AND moduleId = ? AND (ipStart <=
> ? AND ipEnd >= ?)
> UNION ALL
> SELECT id, moduleId,ipEnd, ipStart
> FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
> WHERE subscriptionId = ? AND moduleId = ? AND (ipStart <= ?
> AND ipEnd >= ?)
> UNION ALL
> SELECT id, moduleId,ipEnd, ipStart
> FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
> WHERE subscriptionId = ? AND moduleId = ? AND (ipStart >= ?
> AND ipEnd <= ?)
>
> *Indexes are as follows:*
>
> public class IpContainerIpV4Data implements Data<DefaultDataAffinityKey>,
> UpdatableData<DefaultDataAffinityKey> {
>
> @QuerySqlField
> private long id;
>
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
> "ip_container_ipv4_idx1", order = 1)})
> private int moduleId;
>
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
> "ip_container_ipv4_idx1", order = 0),
> @QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 0)})
> private long subscriptionId;
>
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
> "ip_container_ipv4_idx1", order = 3, descending = true),
> @QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 2,
> descending = true)})
> private long ipEnd;
>
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
> "ip_container_ipv4_idx1", order = 2),
> @QuerySqlField.Group(name = "ip_container_ipv4_idx2", order = 1)})
> private long ipStart;
>
> }
>
>
> *Execution Plan:*
>
> 2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO
> c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - SELECT
> __Z0.ID AS __C0_0,
> __Z0.MODULEID AS __C0_1,
> __Z0.IPEND AS __C0_2,
> __Z0.IPSTART AS __C0_3
> FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z0 USE INDEX
> (IP_CONTAINER_IPV4_IDX1)
> /* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX1: SUBSCRIPTIONID = ?1
> AND MODULEID = ?2
> AND IPSTART <= ?3
> AND IPEND >= ?4
> */
> WHERE ((__Z0.SUBSCRIPTIONID = ?1)
> AND (__Z0.MODULEID = ?2))
> AND ((__Z0.IPSTART <= ?3)
> AND (__Z0.IPEND >= ?4))
> 2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO
> c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - SELECT
> __Z1.ID AS __C1_0,
> __Z1.MODULEID AS __C1_1,
> __Z1.IPEND AS __C1_2,
> __Z1.IPSTART AS __C1_3
> FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z1 USE INDEX
> (IP_CONTAINER_IPV4_IDX1)
> /* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX1: SUBSCRIPTIONID = ?5
> AND MODULEID = ?6
> AND IPSTART <= ?7
> AND IPEND >= ?8
> */
> WHERE ((__Z1.SUBSCRIPTIONID = ?5)
> AND (__Z1.MODULEID = ?6))
> AND ((__Z1.IPSTART <= ?7)
> AND (__Z1.IPEND >= ?8))
> 2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO
> c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - SELECT
> __Z2.ID AS __C2_0,
> __Z2.MODULEID AS __C2_1,
> __Z2.IPEND AS __C2_2,
> __Z2.IPSTART AS __C2_3
> FROM IP_CONTAINER_IPV4_CACHE.IPCONTAINERIPV4DATA __Z2 USE INDEX
> (IP_CONTAINER_IPV4_IDX1)
> /* IP_CONTAINER_IPV4_CACHE.IP_CONTAINER_IPV4_IDX1: SUBSCRIPTIONID = ?9
> AND MODULEID = ?10
> AND IPSTART >= ?11
> AND IPEND <= ?12
> */
> WHERE ((__Z2.SUBSCRIPTIONID = ?9)
> AND (__Z2.MODULEID = ?10))
> AND ((__Z2.IPSTART >= ?11)
> AND (__Z2.IPEND <= ?12))
> 2018-09-03 19:32:03,098 232176 [pub-#78%springDataNode%] INFO
> c.q.a.g.d.IpContainerIpV4DataGridDaoImpl - ((SELECT
> __C0_0 AS ID,
> __C0_1 AS MODULEID,
> __C0_2 AS IPEND,
> __C0_3 AS IPSTART
> FROM PUBLIC.__T0
> /* IP_CONTAINER_IPV4_CACHE."merge_scan" */)
> UNION ALL
> (SELECT
> __C1_0 AS ID,
> __C1_1 AS MODULEID,
> __C1_2 AS IPEND,
> __C1_3 AS IPSTART
> FROM PUBLIC.__T1
> /* IP_CONTAINER_IPV4_CACHE."merge_scan" */))
> UNION ALL
> (SELECT
> __C2_0 AS ID,
> __C2_1 AS MODULEID,
> __C2_2 AS IPEND,
> __C2_3 AS IPSTART
> FROM PUBLIC.__T2
> /* IP_CONTAINER_IPV4_CACHE."merge_scan" */)
>
>
>
--
Best regards,
Andrey V. Mashenkov
