从MySQL检索数据到React Native时提取速度慢

Question

I am not sure what could be causing the fetch to be so slow. Maybe it could be React Native's fetch or maybe it's the way my query is set up?

Here is my fetch for React Native:

    forceUpdateHandler(){
  fetch(`https://www.example.com/React/user-profile.php?username=${this.state.username}` , {
  method: 'GET',
  headers: {
    'Accept': 'application/json',
    'Content-Type': 'application/json',
  }

 })
   .then((response) => response.json())
   .then((responseJson) => {
     this.setState({
       isLoading: false,
       dataSource: responseJson,
       user_image:  responseJson[0].user_images.map(item => ({ ...item, progress: new Animated.Value(0), unprogress: new Animated.Value(1) })),
       },function() {


       });
   })
   .catch((error) => {
     //console.error(error);
   });
}

And here is my PHP Query for the mysql:

 if ($conn->connect_error) {

 die("Connection failed: " . $conn->connect_error);
} 
 // Getting the received JSON into $json variable.
 $json = file_get_contents('php://input');

 // decoding the received JSON and store into $obj variable.
 $obj = json_decode($json,true);

// Populate Username from JSON $obj array and store into $username.
$username = $_GET['username'];

$result = mysqli_query($conn, "SELECT * FROM users WHERE username='$username'");
$fetch = mysqli_query($conn, "SELECT id, images, note, date FROM user_images WHERE username='$username' ORDER BY date DESC"); 

// I think, you'll get a single row, so no need to loop
$json = mysqli_fetch_array($result, MYSQL_ASSOC);

$json2 = array();
while ($row = mysqli_fetch_assoc($fetch)){
    //Our YYYY-MM-DD date.
    $ymd = $row['date'];

    //Convert it into a timestamp.
    $timestamp = strtotime($ymd);

    //Convert it to DD-MM-YYYY
    $dmy = date("m/d/Y", $timestamp);
    $json2[] = array(
        'id' => $row["id"],
        'images' => $row["images"],
        'note' => $row["note"],
        'date' => $dmy,

    );
}
$json['user_images'] = $json2;
echo json_encode(array($json));
$conn->close();

This data only has 5 columns of data, but when I limit it to 1 the react native side fetches the data quickly. Is there a way I can speed up the fetch while still having all of my data results?

Answer 1

1. You should have unuque index on username column on users table. This will also get ride of your assumption that only one row will return. And it will also be faster because MySQL will return when finding one record.
2. (I didn't notice OP is on MyISAM.) It seems that your table is denomorlized and you are using username to query. What if username change? Will you update user_images table?
3. Your user_images table should have an auto_increment primary key, so that your order by can be order by id desc, instead of date. You can have one index on username, but this also depends on how selective of your where statement. If username has a very low cardinality, a full table scan will be chosen by MySQL.
4. You should also try to get the data in one query (join), instead of two.
5. You should use prepared statement for protecting you against SQL injection. Your code is vulnerable now.