CODEWITHSUNDEEP
phpmysql
Hail Hydra
Hail Hydra

Reputation: 473

My request is taking so long to display the info with PHP and MySQL

as says the title I got a problem with a simple sql:

$sql = "SELECT idFactura, strNombre, intFolio, 
               doubleTotal, intTimbrada, intCancelada, 
               dateFechaHora, intStatus 
          FROM tbl_factura  
         WHERE idContador = '".$_SESSION['MM_idContador']."' 
           AND dateFechaHora BETWEEN  '".$_SESSION['MM_Buscar_FechaInicio_F']."' 
                                  AND '".$_SESSION['MM_Buscar_FechaFinal_F']."' 
           AND intTimbrada = 0 
           AND intCancelada = 0 
         LIMIT ?,?";

The table "tbl_factura" has 40,000 records and when a single user is using that part of the system it takes 1 second (sometimes 15 seconds or more) to display the info but if two or more users are using that part it takes sometimes 1 minute or more, to me the sql query is simple but I have no idea what is the problem, every variable passed to the sql statement is set, as I say it works but is very slow when two or more users are using it, I have no idea if is something related with the server.

The OS of the server is Microsoft Windows 6.2 and I'm working with PHP 5.2 and MySQL server 5.1 is a dedicated server.

I tried using the sql statement without "LIMIT" and it's the same, I hope you can give me some ideas.

Here is how I do the connection to the database:

public function __construct()
{
     try {
        $this->dbh = new PDO('mysql:host=localhost;dbname=db_name', 'root', 'xxxxx');
        $this->dbh->exec("SET CHARACTER SET utf8");
    } catch (PDOException $e) {
        print "Error!: " . $e->getMessage();
        die();
    }
}

And here part of the code where execute the query:

$query = $this->dbh->prepare($sql);
        $query->bindValue(1, (int) $offset, PDO::PARAM_INT);
        $query->bindValue(2, (int) $limit, PDO::PARAM_INT);
        $query->execute();

        if($query->rowCount() > 0)
        {

             return $query->fetchAll();

        }

UPDATED

enter image description here

When I do this

SHOW INDEXES FROM tbl_factura;

it display this info

enter image description here

Upvotes: 0

Views: 76

Answers (2)

Jorge Campos
Jorge Campos

Reputation: 23381

As already mentioned by @arkascha there is no INDEXES definition on your table, not even a primary key. So in order to make it run faster you need to define at least the indexes on the fields used on the WHERE clause of your SELECT statement. That would be:

CREATE INDEX idx_idContador ON tbl_factura (idContador);
CREATE INDEX idx_dateFechaHora ON tbl_factura (dateFechaHora);
CREATE INDEX idx_intTimbrada ON tbl_factura (intTimbrada);
CREATE INDEX idx_intCancelada ON tbl_factura (intCancelada);

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71422

Based on your explain, it is clear that you have no indexes on columns where you need them. Typically any column used for one of these operations:

  • JOIN
  • WHERE filter
  • GROUP BY operations
  • ORDER BY operations

must have an index to optimize the query.

In your case, it would appear you need the indexes on the following fields

  • idContador
  • dateFechaHora
  • intTimbrada
  • intCancelada

You should also specify a column to use for ordering if you are doing a LIMIT operation. Without this, you will not be guaranteed to get the same row order from one query to the next. You will also need to add an index on whatever column you use for the ORDER BY condition.

Upvotes: 1

Related Questions