0

We have a long row that includes a json column that holds variable columns specific to each row.

We currently do not index that column as we don't query against it - rather it is supporting data for the result.

That table is now over 100,000 rows and pulling results from it is painfully slow.

We pull the data using its primary key - which is indexed, with a result set no larger than 200, but even at 20... it is very slow.

Slow in the reference is 5-20+ seconds.

The json data is not too large (we have blobs in other tables that are much larger and don't see a performance hit).

Is it a mistake to mark it as a json field? Should we just make it a long text field instead (since we do not actually query the data within it?) and avoid the overhead on the server? Or is there some index setting we are missing to ensure json fields are optimized for data pulls whether or not they are being queried...

Here is the structure of that table:


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- Table structure for caches


CREATE TABLE caches ( id int(11) unsigned NOT NULL AUTO_INCREMENT, audit_id int(11) unsigned NOT NULL, audit_key int(11) unsigned DEFAULT NULL, building_key int(11) unsigned DEFAULT NULL, building_name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, building_id int(11) unsigned DEFAULT NULL, project_id int(11) unsigned DEFAULT NULL, project_key int(11) unsigned DEFAULT NULL, amenity_id int(11) unsigned DEFAULT '0', lead_id int(11) unsigned DEFAULT NULL, lead_key int(11) unsigned DEFAULT NULL, status varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, type varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, type_total int(11) DEFAULT NULL, type_text varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, type_text_plural varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, program_total int(11) DEFAULT NULL, finding_total int(11) DEFAULT NULL, finding_file_status varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, finding_nlt_status varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, finding_lt_status varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, finding_sd_status varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, finding_file_total int(11) DEFAULT NULL, finding_file_completed int(11) DEFAULT NULL, finding_nlt_total int(11) DEFAULT NULL, finding_nlt_completed int(11) DEFAULT NULL, finding_lt_total int(11) DEFAULT NULL, finding_lt_completed int(11) DEFAULT NULL, finding_sd_total int(11) DEFAULT NULL, finding_sd_completed int(11) DEFAULT NULL, address varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, city varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, state varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, zip varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, followup_date date DEFAULT NULL, followup_description text COLLATE utf8_unicode_ci, auditors_json json DEFAULT NULL, amenities_json json DEFAULT NULL, findings_json json DEFAULT NULL, created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, amenity_inspection_id int(10) unsigned DEFAULT NULL, PRIMARY KEY (id), KEY audit_id (audit_id), KEY audit_key (audit_key), KEY building_key (building_key), KEY building_id (building_id), KEY project_id (project_id), KEY project_key (project_key), KEY amenity_id (amenity_id), KEY lead_id (lead_id), KEY lead_key (lead_key), KEY status (status), KEY type (type), KEY finding_file_status (finding_file_status), KEY finding_nlt_status (finding_nlt_status), KEY finding_lt_status (finding_lt_status), KEY followup_date (followup_date) ) ENGINE=InnoDB AUTO_INCREMENT=1019212 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

  • We need to see that slow SELECT. – Rick James Jun 24 at 22:04
  • Explain why audit, building, etc have id, key, and sometimes name. Normally you would have only the id in this table; plus another table with the other columns. – Rick James Jun 24 at 22:07
  • @RickJames one is the local id (in MySsql) - the other is the key from the sync'd API source record – user144875 Jun 24 at 22:32
  • Is there a table of "audits"? With PK(audit_id)? And column audit_key? – Rick James Jun 24 at 22:34
  • 1
    SELECT * FROM caches WHERE audit_id = 123 should not take more than a second for a hundred rows. How many rows have that value? Is there a LIMIT clause? Is Laravel using "lazy" or not-lazy fetching? Do you need all the columns (*)? -- that can also be a source of inefficiency. – Rick James Jun 24 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.