MySQL Forums
Forum List  »  Performance

Re: MySQL performs very very slow
Posted by: Arjen Lentz
Date: February 23, 2005 06:39PM

David West wrote:
> I have been given the approval to post the query.
>
> So here it is.
>
> select w.worksnumber w_num, q.*
> from tblworksprojects w
> LEFT JOIN (select ps.worksnumber
> from tblproject p
> inner join tblprojects ps on
> p.segment1 = ps.ifbisnumber) q
> on w.worksnumber = q.worksnumber
>
> I hope this helps.
>
> It takes about 45828.33 milliseconds on MySQL, 317.66 milliseconds on SQL Server
> and 729 milliseconds on Access. The test results were an average of 3 consecutive
> executions of the query that returned all of the data. The Access test had to use
> a different query so it would run. Also the MySQL box grinds the box to
> a holt when running the query.

Currently, constructs using joins will generally be (much) faster than subqueries and derived tables.
That's just a matter of the optimizer and will of course be improved over time.
In most cases I actually prefer joins over subqueries because it's more readable - but that's somewhat subjective and dependent on your background and experience.

So what are the results when you do the above using a join?


> The number of rows returned by MySQL is 21433, with SQL Server 21457 and
> with Access 21457. The correct number of rows is 21457. So this means that
> MySQL is also reporting the incorrect number of rows.

If the query is deterministic, then this could mean that you've found a bug. Funny though it may sound, that's excellent - that's what alpha versions are for! Please submit a bug report to bugs.mysql.com to our developers can work on it.

Regards, Arjen.
--
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Remote expertise & maintenance for MySQL/MariaDB server environments.

Follow us at http://openquery.com/blog/ & http://twitter.com/openquery

Options: ReplyQuote


Subject
Views
Written By
Posted
13014
February 15, 2005 03:39PM
3700
March 14, 2005 06:13AM
3519
February 18, 2005 03:17PM
3172
February 21, 2005 01:41AM
2977
February 23, 2005 05:29PM
Re: MySQL performs very very slow
3075
February 23, 2005 06:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.