Hi,
On Fri, 12 Nov 2010, Mikael Fridh wrote:
> On Thu, Nov 11, 2010 at 3:47 PM, Gavin McCullagh <gavin.mccull...@gcd.ie>
> wrote:
> > # Time: 101111 14:24:49
> > # u...@host: bacula[bacula] @ localhost []
> > # Query_time: 1139.657646 Lock_time: 0.000471 Rows_sent: 4263403
> > Rows_examined: 50351037
> > SET timestamp=1289485489;
> > SELECT Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId, LStat, MD5
> > FROM ( SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId,
> > File.FilenameId AS FilenameId, LStat, MD5 FROM Job, File, ( SELECT
> > MAX(JobTDate) AS JobTDate, PathId, FilenameId FROM ( SELECT JobTDate,
> > PathId, FilenameId FROM File JOIN Job USING (JobId) WHERE File.JobId IN
> > (9944,9950,9973,9996) UNION ALL SELECT JobTDate, PathId, FilenameId FROM
> > BaseFiles JOIN File USING (FileId) JOIN Job ON (BaseJobId = Job.JobId)
> > WHERE BaseFiles.JobId IN (9944,9950,9973,9996) ) AS tmp GROUP BY PathId,
> > FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM
> > BaseFiles WHERE JobId IN (9944,9950,9973,9996)) OR Job.JobId IN
> > (9944,9950,9973,9996)) AND T1.JobTDate = Job.JobTDate AND Job.JobId =
> > File.JobId AND T1.PathId = File.PathId AND T1.FilenameId = File.FilenameId
> > ) AS Temp JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) JOIN
> > Path ON (Path.PathId = Temp.PathId) WHERE FileIndex > 0 ORDER BY
> > Temp.JobId, FileIndex
> ASC;
>
> Could you please do an EXPLAIN on this query?
I prefixed the query by the word EXPLAIN and ran it:
mysql> source bacularestorequery.sql
+----+--------------------+------------+--------+-------------------------------------+------------+---------+-------------------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+----+--------------------+------------+--------+-------------------------------------+------------+---------+-------------------------+---------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL
| NULL | NULL | NULL | 4277605 | Using where;
Using filesort |
| 1 | PRIMARY | Filename | eq_ref | PRIMARY
| PRIMARY | 4 | Temp.FilenameId | 1 |
|
| 1 | PRIMARY | Path | eq_ref | PRIMARY
| PRIMARY | 4 | Temp.PathId | 1 |
|
| 2 | DERIVED | <derived3> | ALL | NULL
| NULL | NULL | NULL | 4277605 |
|
| 2 | DERIVED | File | ref |
PathId,FilenameId,JobId,jobid_index | FilenameId | 8 |
T1.FilenameId,T1.PathId | 4 | Using where |
| 2 | DERIVED | Job | eq_ref | PRIMARY
| PRIMARY | 4 | bacula.File.JobId | 1 | Using where
|
| 6 | DEPENDENT SUBQUERY | NULL | NULL | NULL
| NULL | NULL | NULL | NULL | no matching
row in const table |
| 3 | DERIVED | <derived4> | ALL | NULL
| NULL | NULL | NULL | 4302683 | Using
temporary; Using filesort |
| 4 | DERIVED | Job | range | PRIMARY
| PRIMARY | 4 | NULL | 4 | Using where
|
| 4 | DERIVED | File | ref | JobId,jobid_index
| JobId | 4 | bacula.Job.JobId | 41816 | Using index
|
| 5 | UNION | NULL | NULL | NULL
| NULL | NULL | NULL | NULL | no matching
row in const table |
| NULL | UNION RESULT | <union4,5> | ALL | NULL
| NULL | NULL | NULL | NULL |
|
+----+--------------------+------------+--------+-------------------------------------+------------+---------+-------------------------+---------+---------------------------------+
12 rows in set (16 min 15.79 sec)
I presume that's what you're looking for?
> Tuning's not going to get any of those 50 million traversed rows
> disappear. Only a differently optimized query plan will.
Well, if the above helps and/or if you'd like me to run an alternative proposed
query I'm happy to. I must confess it would take me quite a few hours to
actually understand that query.
Gavin
------------------------------------------------------------------------------
Centralized Desktop Delivery: Dell and VMware Reference Architecture
Simplifying enterprise desktop deployment and management using
Dell EqualLogic storage and VMware View: A highly scalable, end-to-end
client virtualization framework. Read more!
http://p.sf.net/sfu/dell-eql-dev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
