| NDB and Character Indexes [message #611] |
Fri, 19 January 2007 17:39  |
 TylerC
Messages: 9
Registered: January 2007 |
Junior Member |
|
|
Hey,
I'm fairly new to MySQL's Clustering capabilities, though I am familiar with non-clustered installs. I have been doing performance comparisons between a few database systems, and with MySQL, and also its clustered counterpart. I have found in my tests that when dealing with character indexed queries and millions of rows, clustered queries run orders of magnitude slower than single MyISAM servers with the same information.
Is there something I need to know about the configuration that would increase this performance?
Tyler
|
|
|
|
| Re: NDB and Character Indexes [message #613 is a reply to message #611 ] |
Fri, 19 January 2007 18:17  |
TylerC
Messages: 9
Registered: January 2007 |
Junior Member |
|
|
For example....
TableEmails with 30 million records, an auto-inc( primary key ), an email address(indexed), and an integer status identifier(indexed), with an extra index on status,email.
Now, the query : select * from TableEmails where status=0 and email = 'bleh@bleh.com' takes 20 seconds to return on my ndb setup whereas it takes .2 seconds on MyISAM. Is there some configuration setting that I am missing? Is there some performance tweak I could do to speed this up?
Tyler
P.S. emails will tend to be close to unique... so, the return value on that query is likely to be only 1 or 2 records.
[Updated on: Fri, 19 January 2007 18:53]
|
|
|
| Re: NDB and Character Indexes [message #625 is a reply to message #611 ] |
Mon, 22 January 2007 04:29 |
TylerC
Messages: 9
Registered: January 2007 |
Junior Member |
|
|
Ok, feeling somewhat like an idiot... I wish to issue the problem as I have further unraveled it...
The issue doesn't actually lie in the character index. It appears to be in the AND... Let me explain :
When I run the query : select * from TableEmails where status=0 or the query : select * from TableEmails where email = 'bleh@bleh.com'
the result returns almost instantly. However, when I and them together : select * from TableEmails where status=0 and email = 'bleh@bleh.com', the result takes the 20 seconds.
I have tried creating a new index with both status and email, but that didn't help at all. I have also found this to be an issue on another table that has 5 fields, all indexed integers... when I select, update, delete, etc.. with an AND in the where clause, the query runs INCREDIBLY slower than if I were to run each of the where conditions individually.
Please tell me I am missing something small, a config setting perhaps.
Tyler
|
|
|
|
|
| Re: NDB and Character Indexes [message #628 is a reply to message #627 ] |
Mon, 22 January 2007 05:50 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
Thanks Tyler for your response.
NDB is often slower than local storage engine but it should not be that much slower, good you've found the problem.
I expect NDB cost estimation is not as smart as with other storage engines yet, this is why MyISAM picks up index while it does not.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
