title: Optimizing SQL queries with php
body: <p>700,000 elements in Database
  I have an index on the id's
  I also have an index on province, a varchar of length 30 (although I think the max province has a name of length 12) </p>
  
  <p>SO</p>
  
  <pre><code>    Create table (
       id serial primary key watever,
        price double not null,
        address varchar(200)not null,               
        province varchar(30)not null,
        description text not null,
        status varchar(8) not null,
        type varchar(30) not null,
        category text not null, 
        size int(11) not null,
        bultin int(4),
        bed int(2),
        bath int(2),
        extras text,
        posted_by int foregin key(user.id or whatever)
      );
  </code></pre>
  
  <p>This page takes about ~2 seconds </p>
  
  <pre><code>    $sqlquery = "SELECT DESCRIPTION, ADDRESS, SIZE, BUILTIN, BED, BATH, PRICE 
      FROM listings 
      WHERE PRICE BETWEEN $min and $max AND BED between $minbr AND $maxbr AND CATEGORY = 
      '$cat' AND TYPE = '$type' AND PROVINCE like '%$province%'";
  
      while ($row = mysqli_fetch_array($listings)) {
  
        echo $row['DESCRIPTION'] . " Located at " . $row['ADDRESS'] . " " . $province. "         " 
      . $row['SIZE'] . " sqft" . ", built in " . $row['BUILTIN'] . " " . $row['BED'] . "Bedroom, " 
      . $row['BATH'] . " Bathroom for $" . $row['PRICE'] . "
      &lt;br&gt;
      ";
  </code></pre>
  
  <p>I have gone through some tutorials on increasing the efficiency of SQL queries however they have not changed the speed of what is going on at all.  (Maybe all of the speed lost is in the PHP?!? I think that is highly possible, if so how can I optimize the php?)</p>
  
  <p>EDIT: On phpmyadmin I ran the query, it took as long as it takes to run the php page. The problem is clearly the query and not the PHP.</p>