MySQL :: The addition of JSON column makes SELECT queries non-performant (when temp tables are needed)

Burton
November 27, 2023
250 views
0 votes
2 Answers

I added a JSON column to a table in MySql that holds a small amount of data (~about 7Kb) for each row. The table has roughly 135K rows. Prior to this addition, all my queries on this table executed in well under 1 sec. Now, however, when explain notes a temporary table was needed to for the results, the query takes roughly 7 secs. Does anyone one know why a JSON column would cause such a performance issue? If I change the column to TEXT, the same query run under 1 sec. It stands to reason the JSON column will grow in size… should I pull out this column into another 1:1 table? And would this be an issue if eventually we start querying by values in the JSON column?

It’s worth noting that:

I am not querying the JSON column nor am I returning it in the results.
This table is searched by users is various ways so writing an index for every possible scenario to eliminate the use of temporary tables is not practical.
Most queries are dynamically created via the application Hibernate ORM code

For example, the query below takes 7 secs to complete with a JSON field, but under 1 sec as a TEXT field.

SELECT 
    xaction.XactionId, xaction.PropStreet, xaction.PropNum, xaction.PropStreetUnit
FROM
    Xaction xaction
        INNER JOIN
    Team team ON xaction.TeamId = team.TeamId
        LEFT OUTER JOIN
    AppUser appuser_primaryAgent ON xaction.AppUserIdPrimaryAgent = appuser_primaryAgent.AppUserId
        LEFT OUTER JOIN
    AppUser appuser_coAgent ON xaction.AppUserIdCoAgent = appuser_coAgent.AppUserId
        LEFT OUTER JOIN
    AppUser appuser_assistant1 ON xaction.AppUserIdAssistant1 = appuser_assistant1.AppUserId
        LEFT OUTER JOIN
    AppUser appuser_assistant2 ON xaction.AppUserIdAssistant2 = appuser_assistant2.AppUserId
WHERE
    team.TeamId = 1
        AND (
            appuser_primaryAgent.AppUserId = 1 
            or appuser_coAgent.AppUserId = 1 
            or appuser_assistant1.AppUserId = 1 
            or appuser_assistant2.AppUserId = 1
        )
GROUP BY xaction.XactionId
ORDER BY PropStreet , PropNum , PropStreetUnit

Explain

+----+-------------+----------------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table                | partitions | type   | possible_keys                                                                                                                                                                                            | key     | key_len | ref                                       | rows  | filtered | Extra                                        |
+----+-------------+----------------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | team                 | NULL       | const  | PRIMARY                                                                                                                                                                                                  | PRIMARY | 8       | const                                     |     1 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | xaction              | NULL       | index  | PRIMARY,IDX_PropNum,IDX_PropStreet,AppUserIdPrimaryAgent,AppUserIdCoAgent,XactionPropTypeId,XactionSourceId,XactionStatusId,IDX_PropNum_PropStreet_PropStreetNum,AppUserIdAssistant1,AppUserIdAssistant2 | PRIMARY | 8       | NULL                                      | 49515 |    10.00 | Using where                                  |
|  1 | SIMPLE      | appuser_primaryAgent | NULL       | eq_ref | PRIMARY,AppUserId_idx                                                                                                                                                                                    | PRIMARY | 8       | afdata_next.xaction.AppUserIdPrimaryAgent |     1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | appuser_coAgent      | NULL       | eq_ref | PRIMARY,AppUserId_idx                                                                                                                                                                                    | PRIMARY | 8       | afdata_next.xaction.AppUserIdCoAgent      |     1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | appuser_assistant1   | NULL       | eq_ref | PRIMARY,AppUserId_idx                                                                                                                                                                                    | PRIMARY | 8       | afdata_next.xaction.AppUserIdAssistant1   |     1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | appuser_assistant2   | NULL       | eq_ref | PRIMARY,AppUserId_idx                                                                                                                                                                                    | PRIMARY | 8       | afdata_next.xaction.AppUserIdAssistant2   |     1 |   100.00 | Using where; Using index                     |
+----+-------------+----------------------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------+-------+----------+----------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

And for reference, the table definition is as follows:

CREATE TABLE `xaction` (
  `XactionId` bigint NOT NULL AUTO_INCREMENT,
  `PropNum` varchar(20) NOT NULL,
  `PropStreetDir` varchar(10) DEFAULT NULL,
  `PropStreet` varchar(100) NOT NULL,
  `PropStreetUnit` varchar(20) DEFAULT NULL,
  `City` varchar(100) DEFAULT NULL,
  `State` varchar(4) DEFAULT NULL,
  `Zip` varchar(10) DEFAULT NULL,
  `County` varchar(100) DEFAULT NULL,
  `MlsId` varchar(50) DEFAULT NULL,
  `TaxId` varchar(50) DEFAULT NULL,
  `GfNum` varchar(50) DEFAULT NULL,
  `ListPrice` decimal(13,4) DEFAULT NULL,
  `ListPriceOriginal` decimal(13,4) DEFAULT NULL,
  `SqFt` int DEFAULT NULL,
  `SqFtSource` varchar(50) DEFAULT NULL,
  `Beds` smallint DEFAULT NULL,
  `Baths` decimal(4,2) DEFAULT NULL,
  `YearBuilt` smallint DEFAULT NULL,
  `LotSize` varchar(100) DEFAULT NULL,
  `Schools` varchar(255) DEFAULT NULL,
  `Subdivision` varchar(255) DEFAULT NULL,
  `LockBoxId` varchar(20) DEFAULT NULL,
  `LockBox` varchar(255) DEFAULT NULL,
  `SecurityCode` varchar(255) DEFAULT NULL,
  `HoaFee` varchar(100) DEFAULT NULL,
  `HoaFrequency` varchar(100) DEFAULT NULL,
  `Occupancy` varchar(50) DEFAULT NULL,
  `Remarks` mediumtext,
  `Instructions` mediumtext,
  `ListOtherInfo` mediumtext,
  `ContractPrice` decimal(13,4) DEFAULT NULL,
  `OtherParty` varchar(500) DEFAULT NULL,
  `EarnestMoney` varchar(255) DEFAULT NULL,
  `DueDiligenceFee` varchar(255) DEFAULT NULL,
  `Concessions` varchar(255) DEFAULT NULL,
  `Financing` varchar(50) DEFAULT NULL,
  `SpecialProvisions` mediumtext,
  `ContractOtherInfo` mediumtext,
  `Possession` mediumtext,
  `EffectiveDate` date DEFAULT NULL,
  `ClosingDate` date DEFAULT NULL,
  `ListDate` date DEFAULT NULL,
  `ExpireDate` date DEFAULT NULL,
  `ClosedDate` date DEFAULT NULL,
  `XactionPropTypeId` bigint DEFAULT NULL,
  `XactionSourceId` bigint DEFAULT NULL,
  `XactionSide` varchar(10) NOT NULL,
  `XactionStatusId` bigint NOT NULL,
  `PercentageCommission` decimal(6,5) DEFAULT NULL,
  `CommissionNote` varchar(255) DEFAULT NULL,
  `SplitTeamLead` decimal(6,5) DEFAULT NULL,
  `SplitPrimaryAgent` decimal(6,5) DEFAULT NULL,
  `SplitCoAgent` decimal(6,5) DEFAULT NULL,
  `SplitAssistant1` decimal(6,5) DEFAULT NULL,
  `SplitAssistant2` decimal(6,5) DEFAULT NULL,
  `PayoutEstimated` decimal(13,4) DEFAULT NULL,
  `PayoutActual` decimal(13,4) DEFAULT NULL,
  `PayoutReferral` decimal(13,4) DEFAULT NULL,
  `PayoutBroker` decimal(13,4) DEFAULT NULL,
  `PayoutTeamLead` decimal(13,4) DEFAULT NULL,
  `PayoutPrimaryAgent` decimal(13,4) DEFAULT NULL,
  `PayoutCoAgent` decimal(13,4) DEFAULT NULL,
  `PayoutAssistant1` decimal(13,4) DEFAULT NULL,
  `PayoutAssistant2` decimal(13,4) DEFAULT NULL,
  `ExpensesBroker` decimal(13,4) DEFAULT NULL,
  `ExpensesTeamLead` decimal(13,4) DEFAULT NULL,
  `GeoLatitude` decimal(9,7) DEFAULT NULL,
  `GeoLongitude` decimal(10,7) DEFAULT NULL,
  `GeoLocatorStatus` varchar(10) DEFAULT NULL,
  `TimeZone` varchar(60) NOT NULL,
  `FieldDataJson` text,
  `CreateDateTime` datetime NOT NULL,
  `EditDateTime` datetime NOT NULL,
  `TeamId` bigint NOT NULL,
  `AppUserIdPrimaryAgent` bigint NOT NULL,
  `AppUserIdCoAgent` bigint DEFAULT NULL,
  `AppUserIdAssistant1` bigint DEFAULT NULL,
  `AppUserIdAssistant2` bigint DEFAULT NULL,
  PRIMARY KEY (`XactionId`),
  KEY `IDX_PropNum` (`PropNum`),
  KEY `IDX_PropStreet` (`PropStreet`),
  KEY `AppUserIdPrimaryAgent` (`AppUserIdPrimaryAgent`),
  KEY `AppUserIdCoAgent` (`AppUserIdCoAgent`),
  KEY `XactionPropTypeId` (`XactionPropTypeId`),
  KEY `XactionSourceId` (`XactionSourceId`),
  KEY `XactionStatusId` (`XactionStatusId`),
  KEY `IDX_PropNum_PropStreet_PropStreetNum` (`PropNum`,`PropStreet`,`PropStreetUnit`),
  KEY `AppUserIdAssistant1` (`AppUserIdAssistant1`),
  KEY `AppUserIdAssistant2` (`AppUserIdAssistant2`),
  CONSTRAINT `FK_Xaction_AppUser_Assistant1` FOREIGN KEY (`AppUserIdAssistant1`) REFERENCES `appuser` (`AppUserId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Xaction_AppUser_Assistant2` FOREIGN KEY (`AppUserIdAssistant2`) REFERENCES `appuser` (`AppUserId`),
  CONSTRAINT `FK_Xaction_AppUser_CoAgent` FOREIGN KEY (`AppUserIdCoAgent`) REFERENCES `appuser` (`AppUserId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Xaction_AppUser_PrimaryAgent` FOREIGN KEY (`AppUserIdPrimaryAgent`) REFERENCES `appuser` (`AppUserId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Xaction_XactionPropTypeLookup` FOREIGN KEY (`XactionPropTypeId`) REFERENCES `xactionproptypelookup` (`XactionPropTypeId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Xaction_XactionSourceLookup` FOREIGN KEY (`XactionSourceId`) REFERENCES `xactionsourcelookup` (`XactionSourceId`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Xaction_XactionStatusLookup` FOREIGN KEY (`XactionStatusId`) REFERENCES `xactionstatuslookup` (`XactionStatusId`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB;

Tags: mysql sql

Answers

- slaakso
- November 27, 2023 at 2:51 pm
- 0 votes
0
You do not have an index (or a foreign key defition) for the TeamId which is the one that is likely to limit the query data. The optimizer choose to build a temporary table for that.

Btw, your GROUP BY does not match the columns you have in SELECT. The GROUP BY is used usually with aggregate functions and it should contain all non-aggregated columns from your SELECT.

Login or Signup to reply.

- RickJames
- November 27, 2023 at 6:27 pm
- 0 votes
0
Redesign the schema.
- A "property" and a "transaction" are two separate Entities; they should be two tables. Perhaps there can be multiple transactions for a single property. A 1-many relationship could exist — property_id as a column in the Transaction table.
- Or can it be the other way around — a single transaction involves several properties? This would necessitate a many-to-many table connecting the two Entity tables.
- Instead of having 4 tables for 4 types of people, have a single table with an extra column to indicate primaryAgent, etc. Then there would be one JOIN and a single test for AppUserId = 1. Many of the decimal columns would move into the [up to] 4 rows in the new table.
I also recommend moving "sale" information to a separate table. There would not be a row in that table until the sale occurred. Meanwhile, LEFT JOIN ... IS NULL can determine that the property has not yet sold. Also, if there could be multiple sales, that new table could have multiple rows.

As you restructure the tables, rethink the indexes and foreign keys.

It is unclear whether you need the GROUP BY after these changes.

GROUP BY

Since there are no aggregates, the GROUP BY seems like a waste. On the other hand, I think the restructure will obviate the need for the grouping.

Optimization
- OR is a big performance killer
- The entire table must be read to produce the result.
- By having a single person table and search it by AppUserId = 1, this should avoid 99% of the effort of the query.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.