[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Bacula-devel] small dbcheck patch for mysql


2008/9/9 Bill Moran <wmoran@xxxxxxxxxxxxxxxxxxxxxxx>:
> In response to Bob Hetzel <beh@xxxxxxxx>:
>.  Additionally, doing that
> rewrite so that it results in improvements to all SQL platforms is
> probably going to require that the code be broken into SQL-dependent
> and SQL-independent sections.

It may be that in the critical sections have to do it.

The problem is that different DBMS have different optimizers.
For example, in PostgreSQL (during my tests) operator EXPLAIN does not
show that will be used indices  :

bacula=# EXPLAIN SELECT File.FileId, Job.JobId FROM File LEFT OUTER JOIN Job ON
  (File.JobId = Job.JobId) WHERE Job.JobId IS NULL LIMIT 300000;

                                QUERY PLAN
---------------------------------------------------------------------------
 Limit  (cost=78.86..24169.76 rows=300000 width=8)
   ->  Hash Left Join  (cost=78.86..181966.90 rows=2265021 width=8)
         Hash Cond: (file.jobid = job.jobid)
         Filter: (job.jobid IS NULL)
         ->  Seq Scan on file  (cost=0.00..113937.42 rows=4530042 width=8)
         ->  Hash  (cost=58.38..58.38 rows=1638 width=4)
               ->  Seq Scan on job  (cost=0.00..58.38 rows=1638 width=4)
(7 rows)


However, PostgreSQL worked quickly.

MySQL (theoretically) should use an index (multiple columns), which
already have:
in make_mysql_tables.in
CREATE TABLE File (...
INDEX (JobId, PathId, FilenameId)

but MySQL worked slowly.


-- 
with best regards

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Bacula-devel mailing list
Bacula-devel@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/bacula-devel


This mailing list archive is a service of Copilot Consulting.