พฤหัสบดี, 02 ต.ค. 2014
 
 

SMSKP-PayPal

Donate using PayPal
Amount:
Note:
Note:

PTT Oil Price

Gold Status

SM SKP ADS-1

สมุยสเก็ตอัพ คอมมิวนิตี้
Newsfeeds
Planet MySQL
Planet MySQL - http://www.planetmysql.org/

  • Single thread performance in MySQL 5.7.5 versus older releases via sql-bench
    MySQL 5.7 does much better on benchmarks with high-concurrency. It might do worse on benchmarks with low-concurrency. I am not surprised as this has been true across many releases. The question is whether anything can be done to reverse it. When testing 5.6 I filed bugs 68825 and 69236 for this problem. Maybe it is time for new bug reports. I measure the following from sysbench for InnoDB but must add the disclaimer that I have yet to explain these results and I am wary of unexplained benchmark results. And also note that these overheads are for the sql-bench workload. Your workload might have a smaller overhead. By the same token, if you have less overhead when running TPC-D queries that doesn't mean there isn't a performance regression for short-running queries at low concurrency.First for 5.7.5 compared to 5.0.855.7.5 is 1.45X slower than 5.0.85 when the PS is enabled5.7.5 is 1.33X slower than 5.0.85 when the PS is disabledEnabling PS for 5.7.5 makes it 1.1X slowerNext for 5.6.21 compared to 5.0.855.6.21 is 1.45X slower than 5.0.85 when the PS is enabled5.6.21 is 1.16X slower than 5.0.85 when the PS is disabledEnabling PS for 5.6.21 makes it 1.25X slowerI previously compared MySQL 5.6 to older releases to document performance regressions for single-threaded workloads. This is an important workload even if high-concurrency benchmarks get all of the PR. Database reload and replication apply are cases where low-concurrency performance matters a lot. Others have made a similar case for the importance (Percona, Yoshinori).In this post I present results from sql-bench from the full result and test-wisconsin. I used the same client binary to test all servers -- sql-bench from MySQL 5.5. I think I used the same compiler options and library runtimes for MySQL 5.0, 5.1, 5.5, 5.7 and 5.7. All servers were linked with jemalloc. I tested the following binaries:5.0.85, 5.1.63, 5.5.40, 5.6.21, 5.7.5 - MySQL with PS disabled in my.cnf when supported5.5.40-ps, 5.6.21-ps, 5.7.5-ps - MySQL with PS enabled in my.cnf but no instruments configured5.5.40-ps2, 5.6.21-ps2, 5.7.5-ps2 - MySQL with PS enabled and performance_schema_instrument="wait/io/%"soapboxI think it is odd to publish benchmark results with the PS disabled. It should be enabled for benchmarks with basic instruments configured (rows changed/read/inserted/deleted, IO requests and time per table, similar metrics per user) because the PS does two things -- per user/table monitoring which is always enabled and performance debugging for things like mutex contention which isn't always enabled. There is one time when benchmarks can be run with PS disabled, and that is when searching for the PS overhead.sql-bench test-wisconsinThis result is from the Wisconsin benchmark included in sql-bench for InnoDB. This shows a gradual increase in response time and 5.7.5 is about 10% slower than 5.0.85. The overhead from the performance schema for this workload is small which matches what I have seen before. The PS overhead can be significant for short running queries but is small for others.binary        query-seconds5.0.85           705.1.63           705.5.40           705.5.40-ps        71      5.6.21           715.6.21-ps        73      5.6.21-ps2       73      5.7.5            755.7.5-ps         775.7.5-ps2        77 sql-bench full test innodbThis is the result from a full run of sql-bench for all of the MySQL versions with InnoDB. The create test is much slower starting in 5.6.21 and most of the overhead is in the create_key+drop subtest. The insert, select and transaction tests get slower gradually from 5.0 to 5.7. Only one test, alter table, got faster from 5.0 to 5.7. There is also a significant penalty (25% in 5.6, 10% in 5.7) from enabling the performance schema but no penalty from using performance_schema_instrument="wait/io/%" after enabling the PS.binary        total seconds5.0.85            9245.1.63            9125.5.40            9195.5.40-ps         9885.6.21           10805.6.21-ps        13415.6.21-ps2       13395.7.5            12315.7.5-ps         13435.7.5-ps2        1338This shows the total time relative to the time in seconds for MySQL 5.0.85. Thus the relative time is about 1 for 5.0.85, 5.1.63 and 5.5.40.This has data for all of the test types excluding wisconsin that takes 5 to 7 seconds.version alter-table  ATIS  big-tables  connect create  insert  select transact5.0.85      17        7       5          62      122     528     172     85.1.63      16        7       9          63      102     524     179     85.5.40      17        7       8          65      101     530     178     95.5.40-ps   18        6       8          68      114     579     181     95.6.21      18        7       8          64      219     553     196     105.6.21-ps   18        7       8          70      406     601     213     115.6.21-ps2  18        7       9          66      406     604     212     115.7.5       11        8       9          69      264     630     225     115.7.5-ps    11        8       9          73      335     660     225     125.7.5-ps2   11        8       9          71      332     663     226     12sql-bench full test MyISAMI repeated the test for MyISAM with the PS disabled. There continues to be a regression from 5.0.85 to 5.7.5 but it is smaller than the one for InnoDB -- 1.33X versus 1.16X. So the regression isn't limited to InnoDB. Likely suspects are the parser, optimizer and performance schema (which doesn't narrow the problem much).setupCommand line for test-wisconsin: ./test-wisconsin --server=mysql --host=127.0.0.1 --log --create-options="engine=innodb" --loop-count=1000Command line for full test:./run-all-tests --server=mysql --host=127.0.0.1 --log --create-options="engine=innodb"This is the configuration for MySQL 5.7.5 with the PS disabled. A similar my.cnf file was used for other versions with changes as required:sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES table-definition-cache=1000table-open-cache=2000table-open-cache-instances=8max_connections=20000key_buffer_size=200Mmetadata_locks_hash_instances=256 query_cache_size=0query_cache_type=0server_id=9performance_schema=0binlog_format=rowskip_log_bininnodb_buffer_pool_instances=8innodb_io_capacity=1000innodb_lru_scan_depth=1000innodb_checksum_algorithm=CRC32innodb_thread_concurrency=0innodb_buffer_pool_size=100Ginnodb_log_file_size=1900Minnodb_flush_log_at_trx_commit=2innodb_doublewrite=0innodb_flush_method=O_DIRECTinnodb_thread_concurrency=0innodb_max_dirty_pages_pct=80innodb_file_format=barracudainnodb_file_per_tabledatadir=/data/orig575/var

  • Is the HTTP Plugin for MySQL secure?
    The HTTP Plugin for MySQL offers three APIs: REST-like CRUD, REST-like JSON DOCUMENT and SQL. The SQL API lets you run any SQL you want. Including, for example, DROP mysql.users if you mess up your setup. Insecure? It depends on your viewpoint. It’s more than just another protocol… On the first look HTTP is just another network protocol for sending requests to MySQL. HTTP is the protocol of the web. Whether you need to integrate MySQL in a larger setup and use web services for data exchange or you want to access MySQL from a JavaScript client that is restricted to HTTP/Websocket. HTTP is the natural choice. CouchDB convinced many when it introduced the idea. HTTP Client   Standard client |   | HTTP Protocol   MySQL C/S Protocol |   | MySQL Standard clients use the properitary binary MySQL Client/Server Protocol to communicate with MySQL. The optional HTTP Plugin makes MySQL also listens to HTTP requests. It is you choice whether you want to make MySQL speak HTTP or not. HTTP is a clear-text protocol. Whether clear-text or binary makes no difference: security by obscurity does not work. Both the MySQL Client/Server Protocol and the HTTP Protocol can be run over secure channels. This usally means SSL. SSL is available for both protocols, which means it’s 1:1 again. Let’s do a litte cheat-sheet:   HTTP MySQL C/S Security Rating Obscurity Clear-text Binary 0 : 0 Encryption Supports SSL Supports SSL 0 : 0 The transport layer The MySQL Client/Server Protocol either runs over TCP/IP, Unix Domain Sockets or Windows named-pipes. Unix Doman Sockets are an inter-process communication method for processes on one machine. If you deploy the MySQL Server and its clients on the same machine, you can use this method of communication. It ensures that data exchanged never appears on the network. The data stays on the machine, which adds to the overall security. Although you may use TCP/IP to connect from a client to a MySQL Server on the same machine and the data should stay on the machine, this is no common deployment. Just by using the TCP/IP stack instead of a local IPC method, you loose a tiny bit of security. The TCP/IP stack could send data to the network, the local IPC can’t. It has no means to do so. I assume most deploy MySQL and its clients on different machines. Then TCP/IP is used. TCP/IP is the only choice for the HTTP Plugin. Although you can use SSL to secure the connection, it just a little worse than MySQL C/S:   HTTP MySQL C/S Security Rating Transport TCP/IP TCP/IP, Unix Domain Socket, Windows pipe 0 : 0.5 The access methods: SQL and NoSQL Todays MySQL speaks three protocols: HTTP, MySQL Client/Server Protocol and Memcache (InnoDB Memcache Plugin, MySQL Cluster support). HTTP Client   Standard client   Memcache client |   |   | HTTP Protocol   MySQL C/S Protocol   Memcache Protocol |   |   | MySQL This is done to offer SQL and Not-Only-SQL access methods. The Not-Only-SQL access methods bypass the SQL processing layers in MySQL. A decade ago already, MySQL Connectors folks estimated that SQL parsing can take 50% of the total execution time of a simple query. Yet, it required innovative MySQL expert users and the NoSQL movement until the popular Memcache protocol creeped into MySQL and killed the 50% overhead. In addition to SQL commands, MySQL also accepts key-value style commands. The latter use lower-level APIs inside the server. Thus, they are faster. The Memcache key-value style APIs don’t know about SQL injection or the like – no SQL used… The HTTP Plugin development version we show internally maps all HTTP requests to SQL. It has three APIs. REST-like CRUD and REST-like DOCUMENT perfectly qualify for taking the lower-level API route. Whether we will do that is undecided. If so, SQL injection or the like does not matter – no SQL used… Currently, in the development version, we don’t use the lower-level APIs. That’s no secret, we released the source. In the source one will find the use of SQL and escaping. We could have, had we bothered, used prepared statements. The initial HTTP Plugin development version works exactly like trivial proxy written in a language like PHP, Python or the like. As much as such a proxy can be secured, the current code with its all SQL mapping could be secured. I won’t put any rating entry on the cheat-sheet as this is an implementation detail of a development version. The user authentication Things are now down to one third of the HTTP Plugin – the SQL endpoint – versus a standard MySQL Client. Both a standard MySQL client and a HTTP Plugin client execute SQL as a certain MySQL user. The actions of the client are restricted by the permissions granted. If you allow your client to run DELETE FROM mysql.user you may find yourself in an uncomfortable situation soon. If SSL is used, it is valid to accept MySQL user credentials from the HTTP client and log the client in as the MySQL user given. This clear-text user credential exhange over SSL is also done with the MySQL Client/Server Protocol. It is implementation details of the various pluggable authentication methods MySQL offers that restrict HTTP clients to login as a MySQL user of their choice when SSL is used only. For example, some of the authentication methods require a handshake procedure.   HTTP MySQL C/S Security Rating MySQL user password security Clear-text over SSL Clear-text over SSL and other methods 0 : 0 Without SSL, the HTTP client defaults to a preconfigured user. We used HTTP Basic Authentication, which is a rather undesireable HTTP authentication method, as a simple and immediately to understand method of getting user credentials from a HTTP client. Of course, there are better one! Other HTTP authentication methods are also more complicated and distract. Distract from the SSL explanation. Distract from the option that one could and should see the authentication process as two-staged. The first step is the login towards the HTTP Plugin, then a second step maps the HTTP user to a MySQL user. Wouldn’t it be nice if for non-SSL connections HTTP user ‘Ulf’ could be mapped to MySQL user ‘evil’ and HTTP user ‘Johannes’ could be mapped to MySQL ‘caretaker’? Here, the MySQL users password would not be taken from the HTTP world, it would be taken from some plugin config. First stage (network) HTTP user -> HTTP Authentication method (Basic Auth, OAuth, …) Second stage (inside the Plugin) HTTP user -> Mapping to MySQL user No rating. I find it hard to compare with MySQL where you always have the password in clear-text at the client. What people missed, … The user authentication and mapping alone is still not enough. See slide 100! Slide 100 is what comes after the safe harbour statement. In other words this is pure speculation and idea spreading. No promise we will ever make this happen. HTTP Plugin for MySQL! from Ulf Wendel Here’s the illustration from the slide, slightly simplified (it’s 2am – time for shortcuts ;-)): HTTP Client | Improved security, more freedom | HTTP Plugin app/ v8 JavaScript sql/, crud/, docs/ – built-in The MySQL permission system alone is too coarse to fully match the capabilities of any of todays self-baken HTTP-to-MySQL proxies. You can limit a MySQL user to be allowed to read from a single table only. But, the person can read all rows from the table. You could try to use a view but probably, you would just implement some filtering in your proxy. “Just implement some filtering” becomes a problem when the HTTP Plugin is your proxy. There’s no script language, you can’t do that. Unless, there was a script language built-in to the proxy…   HTTP MySQL C/S Security Rating Application based filtering Implementation detail not bound to protocol Implementation detail not bound to protocol 0 : 0.5 (see text) All in all, a HTTP Plugin can get pretty close to todays solutions. It’s not there yet. Happy hacking! @Ulf_Wendel The post Is the HTTP Plugin for MySQL secure? appeared first on Ulf Wendel.

  • Probably the best European conference on MySQL is coming this fall
    The full schedule for Percona Live London 2014 was revealed earlier this month. Congratulation to all selected speakers and a big thanks to those who submitted a talk. I have many reasons to believe that this conference will be a very good year. Let me explain why: A community event made by community members If you didn’t know who selected the talks for the conference, have a look to the conference committee page. This committee is a perfect mixed between community members and evangelists. As chairman of this conference, I can say that these guys made an amazing job to rate and comment this huge amount of  submitted talks. I particularly want to point out that despite the commitment of some members of the committee for their companies, they played the game with a perfect fair play. They made smart choices with honesty and impartiality. Also, Percona organizes the conference and selects the committee members. But isn’t involved in any way on the choices made by the committee members, it should be highlighted. That mean that the committee works independently, on a fair process, really. I established a new process this year to reinforce this idea of ​​fair selection of the talks. The committee members didn’t have access to the other members’s votes until the end of the rating period. I found this method very interesting to force a spontaneous and personal choice from each committee members. It was probably not perfect but if you have a look to the selected talks, you may find that the selection is particularly renewed this year. if I have the opportunity to work on this event again next year, we probably should involve the whole community in this process. I don’t know how we could make this possible but we have 1 year to think about. Any ideas? One more thing: We did all this for fun. Active sponsors I don’t want to promote the sponsors here but remember that this conference couldn’t be possible without them. I just want to point out that the sponsors are actively involved in the MySQL Community and its development. You probably heard how GitHub works with MySQL or all the good news that Tokutek provides for MySQL and MongoDB. Codership, Continuent, Devart and Severalnines are really active, as usual. Pythian (included Blackbird) is a leader in the world of (big/small)data, don’t miss their blog. And HGST works to improve integration between databases and SSD. Good sponsors make good events! A well balanced schedule Of course, good topics make good events. The predefined categories allowed to have a good sample of topics this year. HA, security, performance, replication… and a lot of case studies, you have no excuse to not attend. The tutorials and the talks offered this year are simply amazing. Also, Facebook is back this year with good stuff, it could be interesting to learn how they manage their small data. Booking, eBay, HP and Spil Games certainly have very interesting things to expose too. And of course, all the latest news about Oracle and MariaDB. You still have doubts? Book now and save money! Hope to see you there next month. Good luck and have fun! PS: Don’t miss the (free) community dinner, because smart attendees make good events!

  • Ambiguous column names (not that kind)
    Time for another quiz to impress your friends, if you have that kind of friends. The manual page in question mysql --skip-enable-disable-column-names=0 -e "select 1" mysql --skip-disable-enable-column-names=0 -e "select 1" mysql --loose-disable-enable-skip-column-names=0 -e "select 1" What's the result? An error about unknown options? A warning? Column headers enabled? Disabled? Answer ▼ mysql --skip-enable-disable-column-names=0 -e "select 1" +---+ | 1 | +---+ | 1 | +---+ mysql --skip-disable-enable-column-names=0 -e "select 1" +---+ | 1 | +---+ mysql --loose-disable-enable-skip-column-names=0 -e "select 1" mysql: option '--skip-column-names' cannot take an argument

  • The Query Rewrite Plugins
    Why Query Rewrites? Now that the cost model project is progressing, most of you are going to notice execution plan changes. In the vast majority of the cases, the changes will be for the better, and some bugs with a long history will finally be closed. In some cases, however, you will notice that your queries run slower. This is inevitable: even if the MySQL optimizer is doing a much better job with the information it has, it may still be the case that the information was incomplete and that the best plan was, in fact, found by not trusting that information! Normally, we would just say “add an optimizer hint” and be over with it. But sometimes you can’t do that. For instance your query could be auto-generated from an application that you have no control over. This is why you want to intervene right before the query reaches the server. And on behalf of Sweden I apologize for Ace of Base. When Query Rewrites? Ideally, a query re-writer should be placed right between the client and the server, to take a quick sniff at the queries coming in and dose the foul-smelling ones with fragrant optimizer hints. But in practice it would probably have to have a complete SQL parser built-in. This would cause a large performance overhead if that re-writer was running on the same machine as the server. A common request has therefore been to be able to write plugins that can pick up the queries at the server side. We now offer two API’s for writing query rewrite plugins. The first one is for when you know exactly – as in character-by-character exactly – what the offending queries look like. This one has a hook to intercept the query string right before it’s parsed. Unsurprisingly, we call it the pre-parse rewrite plugin API. The second one comes in right after parsing and acts on the parse tree. It offers the basic functionality to walk over the parsed query, which is a lot more efficient than dealing with a string. It should not surprise anyone that we call this the post-parse rewrite plugin API. As part of the package there is also a plugin we developed called Rewriter, to which I shall devote the remainder of this post. This plugin is of the latter kind, post-parse. How Query Rewrites? The Rewriter plugin uses a pattern matching machinery to identify the queries that should be rewritten and what they should be rewritten to. The pattern uses wildcard symbols to match constants in the query, and those symbols may be referenced in the replacement. A bit like search-and-replace with capture. I feel the easiest way to explain it is with examples, so I’ve put together a little tutorial. Query Rewrite Tutorial This will be a tutorial on how to get going with your query rewrites. You shouldn’t need more than a running MySQL server and a client to get going. I don’t recommend loading the plugin by simply doing INSTALL PLUGIN. Granted, this will install the plugin for you, but it won’t let you communicate with it. In order to get the whole package you will have to run the provided install script. You should be able to just copy-paste it in your sql client. I just pipe it to the command line client:mysql < <plugin directory>/install_rewriter_plugin.sqlThis will set everything up for you. If you change your mind about rewriting queries, you can just uninstall the plugin with an UNINSTALL PLUGIN command. If you want to completely cover your tracks, you can run the provided script uninstall_rewriter_plugin.sql, but be aware that this will drop all of your rewrite rules without a trace. A simple example Now, let’s rewrite some queries. You do this by defining rewrite rules. A rule consists of a pattern that the queries have to match and a replacement query that will replace all queries that match the pattern. So now that we’re up and running let’s write a rule and hand it to Rewriter. This being a relational database, rules are of course stored in a table. The table is called rewrite_rules and lives in the database query_rewrite. There is nothing magic about this table, it’s a table like any other. We will start with the simplest possible example: we’ll rewrite the query SELECT constant to SELECT constant + 1. So let’s make this  rule INSERT INTO query_rewrite.rewrite_rules( pattern, replacement ) VALUES ( 'SELECT ?', 'SELECT ? + 1' );We have now created a rule in this table, and you may take a look at at in case you forget it later:mysql> select pattern, replacement from query_rewrite.rewrite_rules; +----------+--------------+ | pattern  | replacement | +----------+--------------+ | SELECT ? | SELECT 1 + ? | +----------+--------------+There are other columns in the table as well, but let’s focus on these for now. We have now created a rewrite rule, but we haven’t told Rewriter about it yet. In order to execute rewrites with a minimal effect on performance, the rules have to be loaded into memory. This is done by calling the stored procedure flush_rewrite_rules in the same database as before:CALL query_rewrite.flush_rewrite_rules();This procedure will commit  your current transaction, just like FLUSH TABLE would, and load the rules up in Rewriter’s memory. We are now ready to rewrite some queries:mysql> select 1; +-------+ | 1 + 1 | +-------+ | 2 | +-------+ 1 row in set, 1 warning (0.00 sec)Certainly not what you’d expect when writing SELECT 1! Obviously, something just happened here, so let’s note two things: First, the pattern was “SELECT ?”, yet I wrote “select 1″  - lowercase – and still it obviously matched the pattern. Rewriter, being a post-parse plugin, acts on parse trees rather than strings, so as long as the query and the pattern have the same parse tree, it is considered a match. The second thing to note is that there is a warning. So what might that be? Let’s find out:mysql> SHOW WARNINGS; +-------+------+---------------------------------------------------------------+ | Level | Code | Message                                                       | +-------+------+---------------------------------------------------------------+ | Note  | 1105 | Query 'select 1' rewritten to 'SELECT 2' by plugin: Rewriter. | +-------+------+---------------------------------------------------------------+You didn’t think Rewriter would just rewrite your queries and not tell you, did you? In fact, it will always leave this  note when it rewrites your queries. The really vigilant ones of you will also notice that the original query is spelled out exactly the way I wrote it, in lowercase. This makes the rewrites easy to track in logs. As you saw in the above example, the rule remembers those constant values matched by the ? symbol and injects them into the replacement. The values are injected strictly left-to-right. The syntax uses the same mechanism for matching parts of queries as the prepared statement syntax does: the question mark symbol may be used to match any single literal. So in a sense patterns are like prepared statements run backwards. Where Is My Current Database?! Those of you that really stayed awake during the last section may actually have tried inserting that rewrite rule. You were really apt pupils, trying to be the best in your class. And what did you get for all your hard work? You probably did something like this.mysql> create database mydb; mysql> use mydb;  mysql> create table mytable(a int); mysql> insert into query_rewrite.rewrite_rules( pattern, replacement ) values ( 'select * from mytable where name = ?', 'select "rewritten!"' );  mysql> call query_rewrite.flush_rewrite_rules(); 2014-09-24T13:08:32.198254Z 1 [ERROR] Plugin Rewriter reported: 'Some rules failed to load.'Huh? Failed to load? What’s going on here I hear you saying. Indeed you followed my example to the letter and still you ran into trouble immediately. In order to fix this, let’s gather all information we can from Rewriter. It’s time to see the full contents of the rules table:mysql> select * from query_rewrite .rewrite_rules\G *************************** 1. row ***************************          pattern: select * from mytable where name = ? pattern_database: NULL      replacement: select "rewritten!"          enabled: N          message: Parse error in pattern: >>No database selected<<As you can see, there’s three more columns that I didn’t tell you about: enabled, pattern_database, and message. Enabled is straightforward, the default value is Y, meaning that the rule should be loaded when you load the table. You can set it to N if you want to disable a rule but wish to keep it so you don’t forget it. In this case Rewriter disabled the rule for you because there’s a problem with it. What the problem is can be seen in the message column. Now you’re thinking “but I did select a database! I ran use mydb!” In order to explain why this happens, I will need to go into a technical detail of the loading procedure. So far I’ve tried to keep digressions into the inner workings of the plugin to a minimum, but this one you really need to know about. When you tell Rewriter to load your rules (calling flush_rewrite_rules(), remember?), what it actually does is log in with its own session. Since the current database is a session-dependent setting, Rewriter has no idea what your current session is. Besides, you may have multiple clients logging in an running queries with their own current database. So Rewriter does the sensible thing and doesn’t make any assumptions about what should be considered the current database. That’s what we use the pattern_database column for. Rewriter will use that as the current database when trying to match a query to a rule. Armed with this knowledge, let’s rectify the situation:mysql> update query_rewrite.rewrite_rules set pattern_database = 'mydb', enabled = 'Y'; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> call query_rewrite.flush_rewrite_rules(); Query OK, 1 row affected (0.01 sec)That’s better. This means that the rule will match when you send the query with the unqualified table name mytable and the current database is mydb. Note that the rule will not match queries referencing the qualified table name. If you want to match the query regardless of qualification (and you usually do,) you will have to create two rules, one referencing mydb.mytable and one referencing mytable. This is slightly inconvenient, but it’s a price we have to pay for performance of the pattern matching. I might cover this in more detail in a later blog post, but for now let’s accept it as a fact of life. A Real-World Example In this section, I will cover an actual case where the optimizer lacks complete knowledge about data distribution, and makes an optimistic choice which works best on average but is really slow in the worst case. Those who encounter a worst-case data distribution case will want to nudge the optimizer in the right direction. In this example case we have a health clinic where they frequently need to run the following query:SELECT patientId, time FROM patient_records WHERE patientId > 1000 AND patientId < 2000   AND time < “2014-09-08” AND illness = ”Headache” ORDER BY time LIMIT 1;There are existing indexes on patientId and time. Now, there are two ways we could go when executing this query. Do a range scan on the patientId index, then sort it on time using a filesort and finally to return the first row. Do an index scan on the time field until we find a row that matches the where clause. Intuitively, the second approach seems to be the most efficient one in this case. Since headache is a fairly common gripe, at least among us lowly developers who spend our days hunched in front of terminals, we can assume that we will find a matching row fairly quickly. Note that the optimizer does not have the information on distribution here, so it has to make an educated guess using general heuristics. Indeed, this is how the query is executed after MySQL 5.6. Now, consider if the query were: SELECT patiendId, time FROM patient_records WHERE patientId > 1000 AND patientID < 2000   AND time < “2014-09-08” AND illness = ”Acrocephalosyndactylia” * ORDER BY time LIMIT 1; The illness is different here (I dare you to pronounce it). In this case, a matching row will be rare indeed, if there is one at all. MySQL, still going with option 2, will take 20 seconds to execute this query for the user. There are proposals on how to address this, but it’s not a clear-cut case. That’s why for the time being we recommend adding a FORCE INDEX( patientIdIdx ) clause to the query. The problem is that in this particular case the query was automatically generated by a third-party application that the user couldn’t alter. The solution is now to create a rewrite rule for this particular case: INSERT INTO query_rewrite.rewrite_rules ( pattern, pattern_database, replacement ) VALUES ( # Pattern 'SELECT patientid, time ' || 'FROM patient_records ' || 'WHERE patientid > ? AND patientid < ? ' || 'AND time < ? AND illness = ? ' || 'ORDER BY time LIMIT 1', # Database 'health_clinic', # Replacement 'SELECT patientid, time ' || 'FROM patient_records FORCE INDEX(patientIdIdx) ' || 'WHERE patientid > ? AND patientid < ? ' || 'AND time < ? AND illness = ? ' || 'ORDER BY time LIMIT 1'); This will add the needed hint to each matching query and we’ll get a major improvement in the performance of them. We look forward to your feedback on this new feature! Please let us know if you encounter any problems or have other general input. Thanks! * Acrocephalosyndactylia is a disease that affect one in 100, 000 people.