MySQL 选择查询太慢\

Question

我有一个表，其中有 90 个数据字段和 1500 条记录。当我执行 Select 查询并尝试提取整个表记录时，它变得太慢。即使我将其限制为 100 条记录，也需要超过 30 秒。

如有任何建议，我们将不胜感激。

查询如下:

SELECT 
  ID, FirstName, LastName, 
  date_format( InitialConsultDate , '%m/%d/%Y' ) AS InitialConsultDate, 
  Address1, Address2, City, State, Zip, HomeNumber, WorkNumber, CellNumber, Email, 
  Height, Weight, Age, Sex, Married, BirthDate, UnderPhysicianCare, MedicalIssue, 
  Smoking, Exercise, NumberTimesPerWeek, Dieting, AdequateWater, Cellulite, 
  BiggestConcern, CustomerDeal, LipoSessionsPurchased, FacialSessionsPurchased, 
  NutritionSessionsPurchased, ReferredBy, AdditionalNotes, WeightEvaluationDate1, 
  Weight1, WeightEvaluationDate2, Weight2, WeightEvaluationDate3, Weight3, BMI, 
  ClientBeforePictureFront, ClientAfterPictureFront1, ClientAfterPictureFront2, 
  ClientBeforePictureLeftSide, ClientAfterPictureLeftSide1, ClientAfterPictureLeftSide2, 
  ClientBeforePictureRightSide, ClientAfterPictureRightSide1, 
  ClientAfterPictureRightSide2, ClientMeasurementH1, ClientMeasurementH2, 
  ClientMeasurementH3, Area1Before, Area1After, Area2Before, Area2After, 
  Area3Before, Area3After, IsOldCustomer, ClientAfterFront3, ClientAfterFront4, 
  ClientAfterFront5, ClientAfterLeftSide3, ClientAfterLeftSide4, 
  ClientAfterLeftSide5, ClientAfterRightSide3, ClientAfterRightSide4, 
  ClientAfterRightSide5, ClientBeforeBack, ClientAfterBack1, ClientAfterBack2,
  ClientAfterBack3, ClientAfterBack4, ClientAfterBack5, Area1After1, Area1After2, 
  Area1After3, Area1After4, Area1After5, Area2After1, Area2After2, Area2After3, 
  Area2After4, Area2After5, Area3After1, Area3After2, Area3After3, Area3After4, 
  Area3After5 
FROM customer 
WHERE IsOldCustomer ='0' 
ORDER BY ID

Answer 1

您应该使用索引来优化数据搜索，例如:

CREATE INDEX idx_name ON your_table (field_name);
CREATE UNIQUE INDEX idx_name ON your_table (field_name);

• 对具有重复值的字段使用第一个字段(例如:日期、姓名等。)
• 对具有唯一值的字段使用第二个(例如:登录名、ID 等...)