Rob Drake wrote:
> I've experieced the same problem - here's a benchmark test.
(not really, unless you also provide the table structure and the data ;-)
> Here's the query:
>
> select
> learnercode,language,lastname,left(firstname,1) as
> 'in',gradecode as grade,
> disciplinesnamelang2 as 'Detail'
> from learners as a
> inner join disciplinesaudit as b
> on learnercode=auditlearnercode
> inner join disciplines as c on
> b.disciplinescode=c.disciplinescode order by
> gradecode,learnercode;
>
> The query returns 1817 rows:
>
> In mySQL it takes 2 minutes 40 seconds - (say two minutes forty seconds)
> In SQL Server 2000 it takes 1 second.- (say one second)
>
> And, yes, it's the same PC, same tables in the
> database.
>
> In fact the PC utilisation goes to 100% and pretty much stays there for long enough for me to think
> that the PC is hanging. The mySQL admin shows just about 0% utilisation.
Indexing can be important here, and also various server settings.
In case of MyISAM: record_buffer_size and key_buffer_size
In case of InnoDB: innodb_buffer_pool
The default server settings are for minimum consumption of RAM/disk, not for speed.
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