Hello, i have while loop where i m making select options.
In that loop i have foreach loop:
if($registration){
$dm1 = date('Y-m-d', strtotime('-1 DAY', strtotime($getDrowDate)));
$wasa = new DateTime($getDrow['date_from']);
$wasb = new DateTime($getDrow['date_to']);
$wasb = $wasb->modify( '+1 day' );
$wasperiod = new DatePeriod($wasa, new DateInterval('P1D'), $wasb);
$saone = null;
foreach($wasperiod as $wasdt){
$wasto = $wasdt->format('Y-m-d');
$gResult = mysqli_query($conn,"
SELECT b.registration AS registration, o.city AS city, o.address AS address, o.id AS id
FROM car_bussy AS b FORCE INDEX (dr)
LEFT JOIN car_entry AS e
ON b.incident_id=e.id
LEFT JOIN objects AS o
ON e.object_id=o.id
WHERE
b.registration = '".$registration."'
AND b.date = DATE_SUB('".$wasto."', INTERVAL 1 DAY)
AND e.id!='".$id."'
") or die(mysqli_error($conn));
if(mysqli_num_rows($gResult)>0){
$fRow = mysqli_fetch_array($gResult);
if(0 == $saone++){
echo '<option disabled="disabled" ><< ';
if($registration==$fRow['registration']){
if($fRow['address']!='' && $fRow['city']!=''){
echo $registration.' ('.$description.') '.$fRow['address'].', '.$fRow['city'];
}else{
echo $registration.' ('.$description.') '.$fRow['id'];
}
}
echo '</option>';
}
}else{
if(0 == $saone++){
echo '<option disabled="disabled"><< </option>';
}
}
}
}
Each line takes about 1 second to load!I have tried to put index for all of the tables!And there are not that much data to be loaded ! Machine has power to do this.
Anything i m doing wrong?
Thanks
P.S. if i remove this code it loads fast so this code is the issue
I don’t know how much it would help, but it looks like the code is using only associative keys and not any numeric keys. Why not use the optional $resulttype parameter? http://php.net/manual/en/mysqli-result.fetch-array.php
TBH, it may very well be that anything saved will be relatively insignificant. Depending more or less on how many iterations it goes through. In any case, why ask for something you aren’t using?
I don’t know how many iterations you have, but you can reduce the amount of queries by using the BETWEEN operation to get all records within your time range.
You run the query N times, depending on the date period. In short, this is called “running the query inside a loop,” and it is usually a bad idea.
What you should try instead is to update the query, so you pull all of the information at once with a GROUP BY clause, then parse it using PHP.
Also, be careful with “FORCE INDEX” unless you know exactly what you are doing. As it can hinder the performance of the query if applied wrong.
Please note that in regards to the indexes, you need to test them with data in the table, and then retest them as the data grows. It is normally a bad idea to add indexes to every column in the table, as the query will ONLY use one of the indexes anyway. If you want to have the query use an index across multiple columns, try setting up a composite key/index.
The page becomes exponentially slower as the date range increases since eac day is a separate query. That is unnecessary. The n queries can be condensed into a single query using a between condition as others have said. I take it you didn’t write any of this code? Also in a decade of writing php I have never seen nor heard of force index so I don’t think that is right.
The information provided about the problem should make it possible to do a few Google searches to find out what it means.
With the limited information available of what the code does, why it does it, how the database model is etc. makes it hard to provide any working code.
Take a look on the code under, this is the approach that you need to take. Note that the code might require updates to work perfectly for your case.
$wasa = new DateTime($getDrow['date_from']);
$wasb = new DateTime($getDrow['date_to']);
$gResult = mysqli_query($conn,"
SELECT b.date, b.registration AS registration, o.city AS city, o.address AS address, o.id AS id
FROM car_bussy AS b FORCE INDEX (dr)
LEFT JOIN car_entry AS e
ON b.incident_id=e.id
LEFT JOIN objects AS o
ON e.object_id=o.id
WHERE
b.registration = '".$registration."'
AND b.date = BETWEEN '{$wasa->format('Y-m-d')}' AND '{$wasb->format('Y-m-d')}'
AND e.id!='".$id."'
GROUP BY
b.date
") or die(mysqli_error($conn));
while ($fRow = mysqli_fetch_array($gResult)) {
The “FORCE INDEX” is MySQL specific, but all database systems have a way to tell it which index to use in the query. Under normal circumstances, you do not want to use this functionality, when you deal with large tables and complex queries it can give huge speed benefits. However, it is vital that if you use it that you know what you do as if applied wrong it will kill your query performance.
That would not help the case here, as you would still be executing one query per loop.
As all the time my fault again is i forgot to explain what this do
So lets say its a car rental and i need 5 cars between 1. - 7. Jan to
that makes 5 lines where i can select car (code we are talking about) and driver (both should be unique for a line and day/period).
Part of code i gave is to show where car was before (and there are same code to opposite side that is where it will be)
That is to check and see was/will car be used
I did try your code and i did save some time But now i m not sure if i see that information correct!
Will do some more tests to know!
The code makes more sense with your explanation, though some parts are confusing. That aside, if you have not tried it already I would recommend that you remove the FORCE INDEX part, and see how it perform without that.
Then if you have not tried composite keys, you can give these a try:
ALTER TABLE car_bussy
ADD INDEX datecomposite (date, registration),
ADD INDEX datecomposite2 (registration, date);
Then take your query and run it directly against the database with EXPLAIN prefixed, either through MySQL WorkBench (if you dont have this free database manage software, I strongly recommend to download it), or through phpmyadmin etc. (NOTE: when you know which of the two that is the best match for your table, remove the other one, as each index take up space and will slow down insertion/updates to the table)
Example:
EXPLAIN SELECT b.date, b.registration AS registration, o.city AS city, o.address AS address, o.id AS id
FROM car_bussy AS b
LEFT JOIN car_entry AS e
ON b.incident_id=e.id
LEFT JOIN objects AS o
ON e.object_id=o.id
WHERE
b.registration = '[the info]'
AND b.date = BETWEEN '[from date]' AND '[to date]'
AND e.id!='[the id]'
GROUP BY
b.date
How this forech loop should be run to get same effect then?
But now i m not sure if i see that information correct!