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...
