1

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)

SO

    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)
    );

This page takes about ~2 seconds 

    $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'] . "
    <br>
    ";

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?)

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.

Improve this question
7
  • get a profile tool and analyze the results. Commented Jun 14, 2013 at 10:51
  • 2
    If you have access to your server, run this query directly through MySQL console and see how log it takes. Also, put an EXPLAIN command in front of your SELECT to see what happens behind the scenes. This should be your first step. Commented Jun 14, 2013 at 10:52
  • It took as long as the PHP page! Also I will get a profile tool. Any recommendations? Commented Jun 14, 2013 at 10:53
  • If it took the same amount of time then you know that MySQL is the bottleneck. Run 'explain on it and see what happens. Commented Jun 14, 2013 at 10:54
  • try "explain" your sql in mysql client, add index if any ref needed. Commented Jun 14, 2013 at 10:55

3 Answers 3

Reset to default
1

If you have no idea how indexes work, adding an index on all columns you use in your WHERE and ORDER BY parts would be a start. This is far from optimal, but in most cases its an improvement. Better would be to check with explain or using an optimizer.

So at first, make sure there is also an index on Price, Bed, Category and Type.

Another thing is that you mention you have an index on your Province column, but unfortunately that wont be used if you use LIKE with a wildcard on the beginning. it will only use the part before the first wildcard with the index. So basically this will be a full table scan no matter what index you have.

You could try a FULLTEXT index to speed things up.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

A province list should be short and shouldn't change very often. Make a province table and join to it, so you're doing a wildcard search on a relatively small table, and doing a fully indexed search on your big table.

Improve this answer

1 Comment

Exactly what I was thinking about doing!
0

You've multiple problems in your schema.

This particular query can potentially be optimized with a multi-column index on e.g. (Type, Category, Price, Bed), but imho that will probably do you little good in most cases, because none of these criteria look selective -- not to mention the correlations. (In fact, I'd be surprised if the index gets used at all, except in edge cases, because reading the index will end up too costly compared to a sequential scan.)

The selective criteria is the province, but since you're using a like statement on an unanchored string (as in, it starts with a wildcard), you cannot use a BTree index on it. A temporary fix could be to add a full text index as suggested already. Longer term, you actually want this information to be stored in a format that allows for a spatial index:

http://dev.mysql.com/doc/refman/5.6/en/spatial-extensions.html

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.