-1
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ Does your table have any indexes? \$\endgroup\$ – pacmaninbw Jun 24 '18 at 12:39
  • 2
    \$\begingroup\$ Can you add explain to the start of query and run it on the server with 6000 rows \$\endgroup\$ – Dan Jun 25 '18 at 6:45
  • 1
    \$\begingroup\$ You have two (SELECT MAX(VID) FROM tests) subqueries in your query, and their result is always exactly the same. So why not run SELECT MAX(VID) FROM tests first, store it in $maxVid, compute $comp1 = $maxVid-6 and $comp2 = $maxVid-3 and then run SELECT * FROM tests WHERE VID BETWEEN :comp1 AND :comp2 ORDER BY SID ASC, VID ASC? The whole query doesn't seem to make much sense to me, but that's another matter. \$\endgroup\$ – KIKO Software Jun 28 '18 at 16:00
  • \$\begingroup\$ @pacmaninbw no it dosen't \$\endgroup\$ – Mohsen Newtoa Jun 30 '18 at 13:28
  • \$\begingroup\$ @Dan I uploaded it here mrnewtoa8.000webhostapp.com \$\endgroup\$ – Mohsen Newtoa Jun 30 '18 at 13:29
1
\$\begingroup\$

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).

\$\endgroup\$
  • 3
    \$\begingroup\$ "it took 0.01 to run" 0.01 what? Hours? Centuries? Units are important! \$\endgroup\$ – Mast Jul 2 '18 at 16:46
0
\$\begingroup\$

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);");
\$\endgroup\$
  • \$\begingroup\$ id is already an auto incremented primary key which renders the respective part of your answer useless. and a stored procedure is overkill. The goal of this site is to make a code better but overengineering is anything but an improvement. JFYI \$\endgroup\$ – Your Common Sense Jul 3 '18 at 0:32
  • \$\begingroup\$ also adding an index for SID as well since SID is being used for sorting will be useless as well, because mysql cannot use 2 indices at once. \$\endgroup\$ – Your Common Sense Jul 3 '18 at 8:14
  • \$\begingroup\$ @pacmaninbw i don't know how to use this function :( \$\endgroup\$ – Mohsen Newtoa Jul 4 '18 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.