Mysql – Query Returns 3 Million records

MySQL

The following Query Returns 3 Million records, I want to display only Unique Assets_ID, how to modify this query, so there are no duplicate records?

Select DISTINCT
(a.Assets_ID),
   a.Offices_OfficesID,
   a.AssetInformation_AssetInformationID,
   a.Serial,
   a.AddQuantity,
   a.MEI,
   a.ICCID,
   a.AssetStatus,
   b.AssetType,
   c.Category,
   d.AssetCategories_AssetCategoriesName,
   m.ManufacturerName,
   x.Vehicle_ID,
   x.Emp_Id,
   x.Phone,
   x.QuantityforIssue as qty,
   x.AssignedStatus,
   x.AssignmentDate as stat_date,
   d.AssetCategories_AssetCategoriesName as asst_name,
   u.FirstName,
   u.LastName 
FROM
   Assets as a 
   JOIN
      AssetInformation as b 
      ON a.AssetInformation_AssetInformationID = b.AssetInformation_ID 
   JOIN
      AssetCategories as c 
      ON c.AssetCategories_ID = b.Category 
   JOIN
      AssetStatus as d 
      ON d.AssetStatus_ID = a.AssetStatus 
   JOIN
      Manufacturer as m 
      ON m.Manufacturer_ID = a.Manufacturer 
   JOIN
      AssetAssignment as x 
      ON (x.Asset_ID = a.Assets_ID 
      AND a.Offices_OfficesID = x.Offices_OfficesID) 
   JOIN
      Users as u 
      ON (u.UserID = x.Emp_Id 
      or x.Emp_Id = 0) 
WHERE
   a.Offices_OfficesID IN
   (
      1
   )

Assets table

DESCRIBE Assets
Assets_ID   int(11) NO  PRI NULL    auto_increment  
Offices_OfficesID   int(11) YES MUL 0       
AssetInformation_AssetInformationID int(11) YES MUL 0       
ToolKits_ToolKitsID int(11) YES     0       
Serial  varchar(255)    YES     NULL        
qued    int(11) NO      NULL        
MEI varchar(255)    YES MUL NULL        
ICCID   varchar(255)    YES     NULL        
AddQuantity int(11) YES     1       
AssetCategories_AssetCategoriesID   int(11) YES     0       
User    varchar(50) YES     NULL        
Manufacturer    varchar(50) YES     NULL        
AssetStatus varchar(50) YES     NULL        
DateAdded   datetime    YES     NULL        
Comments    text    YES     NULL

Best Answer

Assuming that Asset_ID is thePRIMARY KEY, you will not have any dups until you do theJOINs. And theDISTINCT` is unnecessary.

Now, let's check the JOINs. What is the mapping between Assets and each other table? Is it 1:1? Or 1:many? Let's say, for discussion, that an Asset is in multiple "categories". At this point, what do you want with SELECT ... c.Category? One solution is

SELECT ..., GROUP_CONCAT(c.Category), ...
    FROM Assets
    JOIN Categories
    GROUP BY Asset_ID  -- this replaces DISTINCT

This avoids exploding the number of rows, and does it by turning that putting a list in the "category" column.

Related Question