MySql 8.0 Read Speed

Question

I'm in need of some expertise here, I have a massive SQL database that I use in conjunction with my Mobile app I program. I'm getting some very long times to fetch a result from the database, at times upwards of 20 to 25 seconds. I've managed to increase the speed and it is were it is now from 40 seconds to retrieve a result. I am hoping someone may have some insight on how I can speed up the query speed and return a result faster then 20 seconds.

Main table is 4 columns + 1 for the "id" column, the database contains 15,254,543 rows of data. Currently it is setup as an InnoDB, with 4 indexes, and it about 1.3GB for size.

My server is a GoDaddy VPS, 1 CPU, with 4 GB of Ram. The is dedicated and I do not share resources with anyone else, its only purpose beside a very basic website is the SQL database.

Just to note, the database record count is not going to get any larger, I really just need to figure out a better way to return a query faster then 20 seconds.

In more detail, the Android app connects to my website via a php document to query and return the results, I have a thought that there maybe a better way to go about this and this maybe were the pitfall is. An interesting note is that when I'm in PHP My Admin, I can do a search and get a result back in under 3 seconds, which also points me to the issue might be in my php document. Here is the php document below that I wrote to do the work.

<?php
require "conn.php";
$FSC = $_POST["FSC"];
$PART_NUMBER = $_POST["NIIN"];

$mysql_qry_1 = "select * from MyTable where PART_NUMBER like '$PART_NUMBER';";

$result_1 = mysqli_query($conn ,$mysql_qry_1);

if(mysqli_num_rows($result_1) > 0) {
    $row = mysqli_fetch_assoc($result_1);
    $PART_NUMBER = $row["PART_NUMBER"];
    $FSC = $row["FSC"];
    $NIIN = $row["NIIN"];
    $ITEM_NAME = $row["ITEM_NAME"];
    
    echo $ITEM_NAME, ",>" .$PART_NUMBER, ",>" .$FSC, "" .$NIIN;

//usage stats   
$sql = "INSERT INTO USAGE_STATS (ITEM_NAME, FSC, NIIN, PART_NUMBER)
VALUES ('$ITEM_NAME', '$FSC', '$NIIN', '$PART_NUMBER')";

if ($conn->query($sql) === TRUE) {
    $row = mysqli_insert_id($conn);
} else {
    //do nothing
}
//

} else {
    echo "NO RESULT CHECK TO ENSURE CORRECT PART NUMBER WAS ENTERED ,> | ,>0000000000000";
}

$mysql_qry_2 = "select * from MYTAB where FSC like '$FSC' and NIIN like '$NIIN';";

$result_2 = mysqli_query($conn ,$mysql_qry_2);

if(mysqli_num_rows($result_2) > 0) {
    $row = mysqli_fetch_assoc($result_2);
    $AD_PART_NUMBER = $row["PART_NUMBER"];

    if(mysqli_num_rows($result_2) > 1){
        echo ",>";
        while($row = mysqli_fetch_assoc($result_2)) {
            $AD_PART_NUMBER = $row["PART_NUMBER"];
            echo $AD_PART_NUMBER, ",  ";
        }
    
        } else {
            echo ",> | NO ADDITIONAL INFO FOUND | ";
        }
} else {
    echo ",> | NO ADDITIONAL INFO FOUND | ";
}


mysqli_close($con);
?>

So my question here is how can I improve the read speed with the available resources I have or is there an issue with my current PHP document that is causing the bottle neck here?

Your question is too wide. What's the specific query that is slow? The first selet, the insert, the second select? Also it's important to indicate if the patterns for the `LIKE` start with a `%` or ends with a `%` (or both). Please be specific. — The Impaler, Jun 28 '20 at 03:40
@Dharman the user input is hidden through the application and encrypted with SSL, I am the only programmer on this, besides myself no one else would know were to even look or guess that its done through php or how to get to the php. I am thinking about changing the script however because this has been commented before on so many other postings about SQL injection attacks, thank you! — , Jun 28 '20 at 22:32
@TheImpaler That is what im trying to figure out, I usually just program in Java on Android, and only do what I need to for PHP and SQL, and thats why I'm trying to understand why the query time is taking so long. From what I've read up on SQL, I thought the best practice was to stay away from "wildcard" queries with the % in it? — , Jun 28 '20 at 22:35
No, you misunderstood. I did not say that someone is about to hack you. I said that you have a bug in your code which you should fix as soon as possible. It does not matter where the data come from. Even you could break your queries accidentally. Just do it properly and parameterize your SQL. — Dharman, Jun 28 '20 at 22:37
@Dharman, Ah I got cha, I'm alright at programming on Android but pretty novice when it comes to SQL and PHP. Do you have an example on how I might be able to correct this code to improve its efficiency and proper execution? Dont need the full script written out for me, I'm up for some work on my end, just need to be pointed in the right direction I suppose. — , Jun 28 '20 at 22:46
See here: https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement — Dharman, Jun 28 '20 at 22:47
@AndroidPrg91 Again, the section of code your are showing includes three SQL statements. Please measure the response time of each one, to find out which one is the slow one. Also, please provide the specific patterns you are using as parameters with the `LIKE` operator; they can make a big difference in terms of performance. — The Impaler, Jun 28 '20 at 23:47
@TheImpaler, the first query finds the part in question, the seconds query adds the returned item to a separate table so I can see how many searches are being done, and the third query scans the database again to fine matching parts that reference the first one found, and provides additional part numbers. I think the first and third queries are were the issue is. The part number which is the search term, contains letters and numbers, either one or the other or both, such as "LD44" for example or "4075S" or "5005." I think this is the parameter you are talking about? — , Jun 30 '20 at 01:29
Should note that the part numbers are associated to the field "NSN" which is a national stock number, consisting of 13 numbers only. Multiple part numbers have matching NSN's which is how it finds alternative part numbers that match. — , Jun 30 '20 at 01:32

score 0 · Accepted Answer · edited Jun 28 '20 at 12:27

0

Instead of using LIKE you would get much faster reads by selecting a specific column that was indexed.

SELECT * FROM table_name FORCE INDEX (index_list) WHERE condition;

The other thing that speeds up Mysql greatly is the use of an SSD drive on the VPS server. A SSD drive will greatly decrease the amount of time it takes to scan a database that large.

edited Jun 28 '20 at 12:27

Dharman

30,962
25
85
135

answered Jun 28 '20 at 04:13

user8555433

71
4

I am using a GoDaddy VPS, I'm not entirely sure what type of hard drives they employ on their servers, and here is the thing, when I go into PHP MyAdmin I can run the same query but get a result back much faster then through the application which leads me to think that its an issue with my PHP script now that I am looking further into it. – Jun 28 '20 at 22:38

MySql 8.0 Read Speed

1 Answers1