I have a complex query that is not able to optimize me with the proper index, ways to optimize questions or indexes Any ideas about modifying Tables Have you tried using the table instead of endless writing? It is not always convenient to bring everything in a query, because it can reduce overall performance For example, I had a complex search query that was written as a single query Seeking value in 7 different tables, it took about 5 seconds to search through 20000 records, for such questions, about a total of about 0.2 seconds after dividing 7 small questions in total time. columndata A table is 55000 records, drows D is less than 4000 record 25 rest of the tables, how should you optimize a problem like this:
select 'id', a.string_data, b.grid_id, c.data_type, d.document_id, 'callmandata' a, 'columnembed' b, `dcolumns` c,` drows` d WHERE b.grid_id = 9 and d.document_id = 17 and d.id = a.row_number and b.column_id = a.column_id and c.id = a.column_id and 0 = (select count (1) `from security` e q,` Role_userlist`f, `as user_type_defaults`g where ((e.access_for = 1 and e.access_for_id = 0) or (e.access_for = 2 and e.access_for_id = F.role_id and f.userid = 0 ) Or (e.access_for = 3 and e.access_for_id = g.id and (g.usertype_name = "or (g.usertype_name =" guest "and 0 = 0)))) and e.access_level = 0 AND ((e .access_type = 2 and e.access_subtype_grid_id = b.grid_id and e.access_subtype_column_id = a.column_id) or (e.access_type = 4 and e.access_subtype_document_id = a.document_id and e.access_subtype_column_id = A.column_id))) by order d Tutorial:
create ABLE `columndata` (` id` int (11) No Auto_increment, .ordering, b.ordering LIMIT, Do not tap `document_id` int (11) No,` column_id` int (11) No tap, `row_number` int (11) no tap,` string_data` varchar (5000), primary key (`id`), index (` column_id `,` Row_number`, `document_id`), index (` row_number`), index (`document_id`)) Not = MyISAM AUTO_INCREMENT = 0 default charset = UTF8; Tab 'callmangid' (`id`int (11) faucet auto_ignment,` parent_id` (11), `column_id`inte (11) tap,` grid_id int (11) tap, 'ordering entry' 11) tap, primary Key (`id`), index (` parent_id`), index (`grid_id`,` column_id`, `ordering`)) engine = mySamAtangentment = 0 default charge = UTF8; Tab Dicolmps` ( `Aidi` Int (11) NOT NULL Oto_inriment,` headers 'varchar (25) NULL, `Deta_prkar' varchar (25) NULL default 'T', create a primary key (` Aidi`)) = Maismm AUTO_INCREMENT = 0 DEFAULT CHARSET = utf8; Tab 'dribbb' (`id`int (11) faucet auto_intermment,` parent_id` et (11), `document_id` int (11) no tap,` grid_id int (11) faucet ',' ordering int '11) Primary Key (`id`), index (` parent_id`), index (`document_id`,` id`, `ordering`)) engine = mysam otangentment = 0 default charge = utf 8; Tab 'security' ( `Aidi` Int (11) NOT NULL Oto_inriment create` access 'ET' (11) NOT NULL for ', `Akses_ For_aid` ET (11) NOT NULL,` Akses_prkar' Aianaf (11) Narwhal `Akses_ Taip_aidi` varchar (11) nOT nULL,` access_subtype_grid_id` integer (11) zero, `access_subtype_column_id` integer (11) zero,` access_subtype_document_id` integer (11) zero, `access_level` integer (4) The default is 0, the primary Key (`id`), index` ind1` (`access_for`,` access_for_id`), index `ind2` (` access_type`, `access_type_id`), index` ind3` (`access_type`,` access_subtype_grid_id`, `acces s_subtype_column_id`), index `Indy 4 (` Akses_prkar`, `Akses_subtaip_dekast_aidi`,` Akses_subteepi_ column_id`)) ENGINE = Maismm AUTO_INCREMENT = 0 DEFAULT CHARSET = utf8; Table `Role_userlist` (` Id` int (11) NOT NULL auto_increment, `userid` int (11) NOT NULL,` role_id` int (11) NOT NULL, `userid_assigning_role` int (11) NOT NULL, PRIMARY KEY (Create `Id`), index (` role_id`), index (`user id ')) engine = mySmAtGenment = 0 default charge = UTF8; Table `#__ Jgrid_user_type_defaults` (` Id` int (11) NOT NULL auto_increment, `usertype_name` varchar (25) NOT NULL,` access_level` integer (11), PRIMARY KEY ( `Id`), Suckank` ind1` (` Create Usertype_name`)) Engine = MySam AUTO_INCREMENT = 0 DEFAULT CHARSET = utf8;
Comments
Post a Comment