mysql - Optimizing left join select query of large data in SQL -

mysql - Optimizing left join select query of large data in SQL -

i know if there faster or improve way of handling next situation:

i find myself struggling optimize execution time of lengthy select query, need create left joins each unique field per record (basically pivoting table). there way minimize single join/pivot cut down time taken retrieve data?

i need create provision 50 custom fields, times out on 30000 odd records (this indexes on relevant columns)

i appreciate help (that includes correcting title i'm not sure how state otherwise) in advance.

below code setup little example:

create table customfield ( id int identity (1,1) not null, fieldname varchar(50) not null, fieldordernumber int not null); alter table [dbo].[customfield] add together constraint [pk_customfield] primary key clustered ( [id] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary]

go

create table fieldvalue ( id int identity (1,1) not null, customfieldid int not null, personid int not null, fieldvalue varchar(50)); alter table [dbo].[fieldvalue ] add together constraint [pk_fieldvalue] primary key clustered ( [id] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary]

go

create table person ( id int identity (1,1) not null, personname varchar (50) not null); alter table [dbo].[person] add together constraint [pk_person] primary key clustered ( [id] asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [primary]

go

insert person (personname) values ('marc'); insert person (personname) values ('john'); insert customfield (fieldname, fieldordernumber) values ('bloodtype',1); insert customfield (fieldname, fieldordernumber) values ('eyecolour',2); insert fieldvalue (customfieldid , personid, fieldvalue) values (1,1, 'a+'); insert fieldvalue (customfieldid , personid, fieldvalue) values (1,2, 'o-'); insert fieldvalue (customfieldid , personid, fieldvalue) values (2,1, 'blue'); insert fieldvalue (customfieldid , personid, fieldvalue) values (2,2, 'hazel'); listfieldvalues (fieldordernumber, fieldname, personid, fieldvalue) ( select cf.fieldordernumber, cf.fieldname, fv.personid, fv.fieldvalue customfield cf left bring together fieldvalue fv on cf.id = fv.customfieldid ) select p.id, p.personname, lvf.fieldvalue column1, --bloodtype lvf2.fieldvalue column2 --eyecolour person p left bring together listfieldvalues lvf on p.id = lvf.personid , lvf.fieldordernumber =1 left bring together listfieldvalues lvf2 on p.id = lvf2.personid , lvf2.fieldordernumber = 2

first need create indexes on identity columns. create indexes on columns you're joining. these tips improve execution time.

mysql sql sql-server