List:General Discussion« Previous MessageNext Message »
From:Brian Bray Date:June 28 1999 5:45am
Subject:Query Optimization Problem
View as plain text  
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)


Any Help would be appreciated!

Thanks in advance,
  Brian


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