Knowing on which columns to put indexes per the known or expected queries is up to the DBA.Īnd as usual in cases like these, take all of StackOverflow advices with grains of salt: you are in charge of the data or programs and have the actual running product in front of you. If a query accesses columns that have high-cardinality but no indexes, too bad. Not withstanding the potentially large algorithmic constants, you will get far bigger big-O algorithmic wins by having proper normalization.ĪNALYZE does not create indexes. Not always the case, but often found in naive SQL queries.ĪNALYZE cannot make up for poor normalization. For example, as with the previous bullet point, something like WHERE SOME_FUNCTION( col ) > some_value means that the query planner likely cannot utilize the indexes as each row must first execute the FUNCTION to get the condition result. Very bad.ĪNALYZE cannot make up for poor queries. This had huge implications as the queries first had to cast the strings to dates (including with errors in the data) before comparison, resulting in multiple entire table scans. The culprit? The schema was storing time and date columns as strings, rather than as native data types. For example, I was recently tasked with speeding up a slow running set of report queries. OPTIMIZE is an expensive operation, requiring a lock on the table, but can be hugely beneficial under certain conditions.ĪNALYZE is table specific, and cannot make up for poor table layout. You can increase the number of sample pages via innodb_stats_persistent_sample_pagesĪNALYZE is not OPTIMIZE, and your tables might also need optimization. By nature, a random sampling means that the statistics gathered might, on any particular run, not be a good representation of the actual data, leading to poor query plans. This generates statistics by sampling the data - looking at a subset of random table rows. The options are to reanalyze tables upon DB start, or to look in to statistics persistence. Without extra measures, this analysis by the DB engine is ephemeral: it will not persist across a database restart/reboot. There are some caveats of which to be aware: The general response then is: update the DB's statistics of the tables in question, and you won't need to provide optimizer hints in your queries: ANALYZE TABLE To your problem at hand - the fleeting usefulness of query plan hints - is that the DB's statistics are not representative of the table data. Unfortunately, MySQL does not make this information readily available, but you can see what you can glean from the INFORMATION_SCHEMA and SHOW INDEXES. In Postgres land, this information is succinctly available via the pg_statistic table. The statistics store details like the key distribution within the indexes, table row sizes, counts, percentage of null rows, distinctness of data, and so on.įor historical (and relevant) performance reasons, the internal database statistics are not updated on-the-fly, but are typically updated periodically at the the DBA's behest. That is, the information the database engine uses to make the query plan. The general concept with which you are dealing is "internal database statistics" (not an official term). If anyone couldn't understand comment your queries. I tried to make the question clear as of my knowledge. It can keep the force index as a possible option to the plan generation If this way is possible then the optimiser need not consider force index every time. So that while choosing the best plan for a query I wanted the optimiser to consider my plan also. Or Else Is there anyway to edit the Code of MySql/PSQL to suggest the Optimiser to use a force index as a possible option during its plan generation.Īdditional Info: I wanted to add my plan to the optimiser plan list( that Optimiser already created many plan for a query). If it is a slow plan on using the forced index then it can use normal query planning. Is there anyway to suggest the Query Optimiser to use a force index as a possible option during its plan generation. And the force index which I used may not be the right index search for that query. Then after some times the data in that table may change. There are times I can use force index option to use particular index on a query to make the query faster.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |