Very slow query on table with Blob column I have a table with approximately 3500 rows.  This table contains a longblob column with an average blob size of 750kb.  My query performance really deteriorates when trying to filter on the primary key column.

Select * from tblphotos  yields a response in 1 second, returns 3500 rows
Select * from tblphotos where ActLogId = 20992   yields a response in 130 seconds, returns 6 rows.

I'm looking for any sort of rationale as to why this is so slow and how to make this quicker.  Moving from ROW_FORMAT=COMPACT to DYNAMIC improved performance about 30%.

CREATE TABLE `tblphotos` (
  `PhotoID` int(10) NOT NULL,
  `ActLogID` int(10) DEFAULT NULL,
  `Order` smallint(5) DEFAULT NULL,
  `PhotoFile` longblob,
  `PhotoA` varchar(5) DEFAULT NULL,
  `Caption` varchar(100) DEFAULT NULL,
  `Entered` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `tblphotos`
--

INSERT INTO `tblphotos` (`PhotoID`, `ActLogID`, `Order`, `PhotoFile`, `PhotoA`, `Caption`, `Entered`) VALUES
(1, 19264, 1, blobvalued, 'JPG', 'Nameplate', NULL);


ALTER TABLE `tblphotos`
  ADD PRIMARY KEY (`PhotoID`),
  ADD KEY `ActLogID` (`PhotoID`,`ActLogID`,`Order`,`PhotoA`,`Caption`,`Entered`,`PhotoFile`(20)) USING BTREE;


-- AUTO_INCREMENT for table `tblphotos`
--
ALTER TABLE `tblphotos`
  MODIFY `PhotoID` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3421;
You need an index starting with ActLogId.

The indexes with lots of columns are likely to be not very useful.  Recommend getting rid of them, and redesign the indexes.

"Prefix" indexing (PhotoFile(20)) is rarely useful.

Read this cookbook for tips on creating the optimal index for a query.

where PhotoID &gt; 0 AND ActLogId = 20992 needs a composite INDEX(ActLogId, PhotoID), in that order (= first).