trjhtr

I just made this link please check this out Online Results

I need to select data between 2 values from a table by using MAX because each SID has different VID.

My code:

$sql = mysqli_query($con, "SELECT * FROM `tests` WHERE `VID` BETWEEN (SELECT MAX(`VID`) FROM `tests`)-6 AND (SELECT MAX(`VID`) FROM `tests`)-3 ORDER BY `SID` ASC, `VID` ASC;");

it works on a small table:

INSERT INTO `tests` (`ID`, `SID`, `VID`, `Text`) VALUES
(1, 1, 1, 'test'),
(2, 1, 2, 'test'),
(3, 1, 3, 'test'),
(4, 1, 4, 'test'),
(5, 1, 5, 'test'),
(6, 1, 6, 'test'),
(7, 1, 7, 'test'),
(8, 1, 8, 'test'),
(9, 1, 9, 'test'),
(10, 1, 10, 'test'),
(11, 1, 11, 'test'),
(12, 2, 1, 'test'),
(13, 2, 2, 'test'),
(14, 2, 3, 'test'),
(15, 2, 4, 'test'),
(16, 2, 5, 'test'),
(17, 2, 6, 'test'),
(18, 2, 7, 'test'),
(19, 2, 8, 'test'),
(20, 3, 1, 'test'),
(21, 3, 2, 'test'),
(22, 3, 3, 'test'),
(23, 4, 1, 'test'),
(24, 4, 2, 'test'),
(25, 4, 3, 'test'),
(26, 4, 4, 'test'),
(27, 4, 5, 'test'),
(28, 4, 6, 'test'),
(29, 4, 7, 'test'),
(30, 4, 8, 'test'),
(31, 4, 9, 'test');

However, on a table with over 6000 rows it does not complete in any reasonable time.

First ID appears to be monotonically increasing, and appears to be the primary key. If so then when you declare ID in the table definition you can use auto increment and you don't need to include it in the insert statement values. This may improve the performance of the insert statements because there is less I/O.

INSERT INTO `tests` (`SID`, `VID`, `Text`) VALUES
 (1, 1, 'test'),
 (1, 2, 'test'),
 (1, 3, 'test'),
 (1, 4, 'test'),
 (1, 5, 'test');

Second, adding an index on VID will improve the performance of searching the VID column, which is what you are doing with SELECT MAX(VID). Select statements on a column that isn't indexed can cause timeouts, especially with large amounts of data.

CREATE TABLE IF NOT EXISTS `tests` (
 `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `SID` INT NOT NULL,
 `VID` INT NOT NULL,
 `Text` VARCHAR(16),
 PRIMARY KEY (`ID`),
 INDEX `VID_idx` (`VID` ASC)
);

@KIKOSoftware has a valid point, the sub query SELECT MAX(VID) is performed twice and it would improve performance by storing the value of the sub query in a variable and only performing the sub query once.

Another possible performance enhancement might be to put the whole statement into a stored procedure. Passing parameters into the stored procedure allows the calling program to change the upper and lower limits without modifying the stored procedure.

The stored procedure might contain the following

DROP procedure IF EXISTS `MyStoredProcedure`;
CREATE PROCEDURE `MyStoredProcedure`
(
 LowerOffset INT,
 UpperOffset INT,
)
BEGIN
 SET @MaxVID = SELECT MAX(VID);
 SET @UpperLimit = @MaxVID - UpperOffset;
 SET @LowerLimit = @MaxVID - LowerOffset;
 SELECT * FROM `tests` WHERE `VID` BETWEEN @LowerLimit AND @UpperLimit ORDER BY `SID` ASC, `VID` ASC;

END

It may be wise to add an index for SID as well since SID is being used for sorting.

The PHP statement now becomes:

$sql = mysqli_query($con, "CALL MyStoredProcedure(6,3);");

6000 records is absolutely nothing for a database, even with 3 full scans. I downloaded your dump and run your query on my ordinary PC, it took 0.01 to run. So I just cannot imagine the amount of overselling on that 000webhostapp.com.

Either way, if you want your query to run fast with 600 000 records, add an index for VID field,

alter table tests add key (VID);

this is all you need for this query.

(and if even after that it will be slow, then something really terrible is about that hosting provider you are using at the moment).