Today, the user responded to a CI list page and opened it very slowly.
View Code It is found that the SQL statement used to load the list is composed of multiple left join tables.
Select nm. ID, nm. model_id, NMM. code, nm. serialcode, nm. manufacturer, nm. maintainer, nm. state, nm. type, nm. deleted, nm. remark,
DP. DeviceID, D. Name as d_name, DP. portname,
I. Name as I _name, E. Name as e_name, P. Code as p_name
From netmodule nm
Left join netmodule_model NMM on NMM. ID = Nm. model_id
Left join netdeviceport DP on DP. linkmoduleid = Nm. ID
Left join net_device D on D. id = DP. DeviceID
Left join (select * From equipment_position where configitemid = 5) em on em. equipmentid = DP. DeviceID
Left join t_idc I on I. ID = em. idcid
Left join t_equipment e on E. ID = em. shelfid
Left join idc_position P on p. ID = em. positionid
Remove left join one by one, troubleshoot all the way, and find that as long as left join netdeviceport is a very slow table! The other has almost no effect on the query speed.
I tried to connect the ID field of the table to the left (ID is an auto-incrementing ID), and found that the speed is very fast, but it will be very slow as long as it is associated with fields other than ID!
So I added an index to this field. It is still slow!
After reading the netdeviceport table, there are about 80 thousand data records. Is it because of the large amount of data?
There are tens of thousands of data records in other tables. Why is it slow?
One night later, I found that the engine of the netdeviceport table is different from that of other tables. Others are InnoDB, and only MyISAM !!
So assimilation: alter table netdeviceport engine = InnoDB
A quick query!
It turned out to be caused by different engines!