Re: MySQL Table Query Speeds versus MS SQL
Thank you so much for spending time replying to my questions
> How do you know it was uncached?
I ran the code with this on top of the MS SQL query:
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
*****************************
> Is UNQ_CHECK_NBR NULLable?
This field can be NULL, but I am not sure that answers your question. NN is not checked for this field.
> 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.
The query you suggested runs about 10% faster than the first query.
*********************************
>
> Please provide SHOW CREATE TABLE cid_steve;
'cid_steve', 'CREATE TABLE `cid_steve` (\n `SKU_ITEM_KEY` varchar(20) DEFAULT NULL,\n `UNQ_CHECK_NBR` varchar(36) DEFAULT NULL,\n `ITEM_COUNT` int(11) DEFAULT NULL,\n KEY `idx_UNQ_CHECK_NBR` (`UNQ_CHECK_NBR`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED'
(compression doesn't seem to impact the speed)
*************************************
>
> > '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.
I have shut off query caching for testing, but with query caching on, the second query is instant. I run it a couple times to get the disks warmed up though.
Indexes are a bit tricky because of the time they take to create. It is worth it in the long run, but there is no 'long' run in many of our use cases. A count, query, join, sub queries etc will be done one time in a DB and then the output will be moved to the next step. Not to mention it took 3-5 minutes to index the UNQ_CHECK_NBR field on MySQL and a fraction of a second on mssql.
**************************
>
> But... You needed to tune some things...
> innodb_buffer_pool_size = 50G -- for InnoDB
=256G
> key_buffer_size = 30G -- for MyISAM
=384M
Is that a problem? We aren't utilizing MyISAM right now.
> 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.
Agreed, we are going to expand testing to an whole 'use case'. Perhaps trying it on MyIsam as well
>
> > UNQ_CHECK_NBR is indexed
>
> By itself?
Yes
> What datatype?
VARCHAR(36)
> UNIQUE?
No
>
> > 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.)
Before getting to this point, I added a primary key called “ID” with auto_encrement but it only gave a 5-10% performance boost.
I think it took about 20 minutes to add a primary key column. Again, not worth the time on a table that is going to be 'used' once.
> > 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.
Worth pursuing.
>
> > 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??
It’s typical in that it could be one of the first steps in a large query and done one time. But it's not done day after day on the same table. A more typical case would include many more steps along with various joins and calculations with the output moved onto the next step.
>
> > 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.
Would a summary table make sense in a table that has only 3 columns?
>
*************
I'm not down on MySQL at all. It is an amazingly polished product. We want to make every effort to see if a system like this could replace about 1/2 our MS SQL installation. Unless we can approach half, we don't think it is worth the trouble to run 2 different DB systems.
We are not looking to match or exceed MS SQL 2008. If we could run ½ to 1/3 the speed of MS SQL we could likely leverage it for many projects.
It seems like we are 10x slower on MySQL which seems too far off to be accurate. i.e. I must be doing something wrong (which brought me here)