List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:June 28 1999 3:06pm
Subject:Re: Query Optimization Problem
View as plain text  
Brian Bray wrote:
> 
> I'm having a problem with a query that is taking forever.
> Using the EXPLAIN syntax I've been able to figure out
> where to add a few indexes that speed it up a little
> bit but I cant seem to speed it up any more.  With about
> 18,000 records in the Bookmarks table the query is taking
> up to 5 seconds to finish (this is a web app so 5 seconds
> is unacceptable) and I'm expecting to have millions of
> records in the Bookmarks table.
> 
> Here are the tables:
> 
> mysql> describe Bookmarks;
> +------------------+--------------+------+-----+-------------+----------------+
> | Field            | Type         | Null | Key | Default     | Extra
> |
> +------------------+--------------+------+-----+-------------+----------------+
> | id               | int(11)      |      | PRI | 0           |
> auto_increment |
> | title            | varchar(255) | YES  |     | NULL        |
> |
> | url              | varchar(255) | YES  |     | NULL        |
> |
> | folderId         | int(11)      |      | MUL | 0           |
> |
> | userId           | int(11)      |      | MUL | 0           |
> |
>  ......
> +------------------+--------------+------+-----+-------------+----------------+
> 20 rows in set (0.00 sec)
> 
> mysql> describe Folders;
> +-------------+--------------+------+-----+---------+----------------+
> | Field       | Type         | Null | Key | Default | Extra          |
> +-------------+--------------+------+-----+---------+----------------+
> | id          | int(11)      |      | PRI | 0       | auto_increment |
> | title       | varchar(255) | YES  |     | NULL    |                |
> | userId      | int(11)      |      | MUL | 0       |                |
>  ......
> | parentId    | int(11)      | YES  |     | 0       |                |
> +-------------+--------------+------+-----+---------+----------------+
> 14 rows in set (0.01 sec)
> 
> mysql> describe Subs;
> +-----------+----------+------+-----+---------+----------------+
> | Field     | Type     | Null | Key | Default | Extra          |
> +-----------+----------+------+-----+---------+----------------+
> | id        | int(11)  |      | PRI | 0       | auto_increment |
> | userId    | int(11)  |      | MUL | 0       |                |
> | folderId  | int(11)  |      | MUL | 0       |                |
>  ......
> +-----------+----------+------+-----+---------+----------------+
> 7 rows in set (0.01 sec)
> 
> And the query:
> SELECT Bookmarks.* FROM Bookmarks, Folders LEFT JOIN Subs ON
> Folders.id=Subs.folderId
> WHERE Bookmarks.folderId = Folders.id AND (Folders.userId=1 OR
> Subs.userId=1);
> 
> And the EXPLAIN CLAUSE:
> mysql> explain SELECT Bookmarks.* FROM Bookmarks, Folders LEFT JOIN Subs
> ON Folders.id=Subs.folderId
>     -> WHERE Bookmarks.folderId = Folders.id AND (Folders.userId=1 OR
> Subs.userId=1) ;
>
> +-----------+--------+----------------+---------+---------+--------------------+-------+------------+
> | table     | type   | possible_keys  | key     | key_len | ref
> | rows  | Extra      |
>
> +-----------+--------+----------------+---------+---------+--------------------+-------+------------+
> | Bookmarks | ALL    | folderId       | NULL    |    NULL | NULL
> | 18170 |            |
> | Folders   | eq_ref | PRIMARY,userId | PRIMARY |       4 |
> Bookmarks.folderId |     1 |            |
> | Subs      | ALL    | folderId       | NULL    |    NULL | NULL
> |     1 | where used |
>
> +-----------+--------+----------------+---------+---------+--------------------+-------+------------+
> 3 rows in set (0.00 sec)

Make all your columns NOT NULL. If that doesn't speed it up enough,
change the order of the WHERE clause. Still not good enough? Do a "show
keys" for each table and send the results.

  jim...
Thread
Rewriting of subselect queryTor-Erik Hagen27 Jun
  • Re: Rewriting of subselect queryMartin Ramsch28 Jun
  • SV: Rewriting of subselect queryTor-Erik Hagen28 Jun
    • Query Optimization ProblemBrian Bray28 Jun
      • Re: Query Optimization ProblemJim Faucette28 Jun
      • Re: Query Optimization ProblemChristian Mack15 Jul