Re: MySQL Table Query Speeds versus MS SQL
Posted by:
Rick James
Date: August 09, 2014 11:17PM
> the same (uncached) query runs in ...
How do you know it was uncached?
Is UNQ_CHECK_NBR NULLable?
By definition, this needs to check each UNQ_CHECK_NBR for NULL:
select COUNT(UNQ_CHECK_NBR) as Total_Baskets
from cid_steve D ;
select COUNT(*) as Total_Baskets
from cid_steve D ;
will count the rows in the table more efficiently.
Please provide SHOW CREATE TABLE cid_steve;
> 'Using index'
Implies that it read the index only, not the data. Each secondary index is kept in a separate BTree, separate from the data. Depending on the datatypes, etc, that can be fast or faster. (Hence, the need to see CREATE TABLE.)
Did you run the query twice? (Back to caching.) The first time, MySQL will fetch the needed rows from disk (index rows, in this case); the second time, the rows will be in your very generous RAM.
But... You needed to tune some things...
innodb_buffer_pool_size = 50G -- for InnoDB
key_buffer_size = 30G -- for MyISAM
If you left them at their defaults, you were possibly I/O bound unnecessarily. (I pulled those setting out of a hat; you have such a big machine, that almost any big number would work equally well.)
There are a number of things that could (and should) be tweaked between database vendors. A single COUNT() is not much of a benchmark. Try some more of the queries that are important to your application.
> UNQ_CHECK_NBR is indexed
By itself?
What datatype?
UNIQUE?
> there is no primary key defined
This tends to be sub-optimal for InnoDB. A simple INT UNSIGNED AUTO_INCREMENT is always better than no PRIMARY KEY. (Note: A PK in MySQL is, by definition, UNIQUE and NOT NULL.)
> optimization script to help with the mysql config (as much as a noobie can I suppose).
Yeah, that script was probably written when 2GB was a huge RAM.
> bad performance on full table queries
If you have 34M-row tables, you should design your schema and application to avoid full table scans. It could well be that MSSql has more smarts in this area.
> Runs much, much better on Linux?
MySQL is better off on Linux than Windows. But I would not say "much, much better".
> I'm sure there are other areas where MySQL excels
There are a few.
> but this is a very typical use case for me
COUNT(column) is typical??
> handful of DBA's running queries and transforming large data sets that will be moved into a reporting application when processing is complete.
A "Data Warehouse" application? Summary tables are the best tool.
> Could this be an issue of single cpu/core queries?
MySQL will use only one core per connection. So, your 80 cores will go begging.