MySQL Forums
Forum List  »  Newbie

Re: MySQL Table Query Speeds versus MS SQL
Posted by: Rick James
Date: August 12, 2014 10:13AM

CREATE TABLE `cid_steve` (
 `SKU_ITEM_KEY` varchar(20) DEFAULT NULL,
 `UNQ_CHECK_NBR` varchar(36) DEFAULT NULL,
 `ITEM_COUNT` int(11) DEFAULT NULL,
 KEY `idx_UNQ_CHECK_NBR` (`UNQ_CHECK_NBR`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
Is this a mapping between SKUs and UUIDs? Is it 1-1?

Can any of the fields ever be NULL? (If not, state them as NOT NULL.)

InnoDB really needs an explicit PRIMARY KEY. If it is 1-1 mapping then this is warranted:
PRIMARY KEY(SKU_ITEM_KEY), -- a PK, but definition, is UNIQUE and NOT NULL.
INDEX(UNQ_CHECK_NBR) -- optionally UNIQUE, if you want a constraint.
(Or vice versa)
1-to-many would similar, but not allowing UNIQUE in one of the directions.

VARCHAR(36) smells like a UUID. Such animals are horrible to index because they are so random. The one mitigating factor is that the table is (currently) only about 3GB (see SHOW TABLE STATUS) and you have plenty of room to cache it all.

That brings up another issue -- When you start up mysql, all caches are cold. That means any query (especially yours) will do a lot of I/O. How fast does your query run when you run it a second time (and all the data is cached)? I'll guess it is 10x faster.

Cold caches is (generally) not a good way to benchmark a "production" machine. A production machine will "never" go down, so everything that can be cached, is. So, for benchmarking, run each query twice, and count the second timing, not the first.

> I'm not down on MySQL at all. It is an amazingly polished product.

Yeah, I guess I sound defensive.

> We want to make every effort to see if a system like this could...

And we will help you. Please note that some MSSql techniques (datatypes, SELECTs, etc) may need modifying to pull out the full potential of MySQL.

> If we could run ½ to 1/3 the speed of MS SQL

I suspect you will find some queries run just as fast, while others run much slower. I hope "½ to 1/3" is an "overall goal", not specific to each query.

> It seems like we are 10x slower on MySQL

Aha -- see if my caching comment answered that issue.

> Can you perhaps show me a use case (table design and query) of a join and query or other operation that will be as fast as ms sql?

This could be done. However, note that it depends a lot on the indexes on the tables. MySQL likes to do "Nested Loop Join" and shuns "Index Merge (Intersection, etc)", whereas others (MSSQL?) like to do Merges. Merges are inherently less efficient most of the time, but users get lulled into accepting the speed. Then they go to MySQL which fails to find a decent INDEX to do NLJ, and runs slower. This _may_ be an example of how we make MySQL run faster than MSSql, simply by adding an index.

When you get to testing the JOINs, please provide
SHOW CREATE TABLE
SHOW TABLE STATUS (or otherwise indicate the table size)
EXPLAIN SELECT ...

Nothing obviously bad in the my.cnf (except maybe the key_buffer_size). Later, when you have some 'real' queries running, I may ask for SHOW VARIABLES and SHOW GLOBAL STATUS, in order to give a more further analysis.

Options: ReplyQuote


Subject
Written By
Posted
Re: MySQL Table Query Speeds versus MS SQL
August 12, 2014 10:13AM


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.