Timeline for MySQL How to Script to create indexes on all date columns in database
Current License: CC BY-SA 4.0
26 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 3 at 21:07 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jul 5 at 21:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Mar 6 at 14:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Nov 7, 2023 at 4:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jul 10, 2023 at 1:04 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Mar 8, 2023 at 21:07 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Nov 6, 2022 at 10:04 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jul 7, 2022 at 9:07 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Mar 6, 2022 at 15:00 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Oct 31, 2021 at 16:05 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jul 2, 2021 at 18:03 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Mar 3, 2021 at 9:06 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Apr 29, 2020 at 3:59 | answer | added | Rick James | timeline score: 0 | |
| Apr 29, 2020 at 3:50 | comment | added | StixO | @RickJames you are right in that regards , but I am looking for a quick way to add the index on the date columns in every table .. if you have 150 tables and you want something really quick , not knowing what has the index and what does not .. better to run through and at least add. Almost all if not all the queries are going to be simply Between begin and end dates. And All I am getting is you should do this or that - all fine points but none of those things gets the result I was looking for. | |
| Apr 28, 2020 at 23:58 | comment | added | Rick James |
If your typical query is WHERE foo_id = 123 AND date BETWEEN..., then INDEX(foo) is not nearly as good as INDEX(foo_id, date).
|
|
| Apr 22, 2020 at 10:30 | comment | added | StixO | @Akina How can I accomplish this - if I know how to do this - I will know how to script for the other columns if I need them - I know any field with value of 0 and 1 has low cardinality and not really to useful to index. But how can I accomplish the task .. | |
| Apr 22, 2020 at 9:18 | comment | added | Akina | just for reference - consider the use case similar to This is exactly the case when you need a composite index (in all 3 answers!), and indexes of one field each for all fields do not make sense. | |
| Apr 22, 2020 at 0:32 | comment | added | nbk | yes i know that, the problem starts that i can't verify your selects statements, so that building a complex query withouit having any test data is quite difficult or evenimpossible. So the solution with the procedure, is not that complex and easily to realize. But as i stated before without verufying your select , it is also difficult till impossible to program that also. You understand what is missiing ist the tables to test your querys | |
| Apr 22, 2020 at 0:26 | comment | added | StixO | @nbk the process I know - the code posted above is basically the components of that process - I am having trouble putting them all together in one script . | |
| Apr 21, 2020 at 23:54 | comment | added | nbk | The solution is simple build stored procidrue . In that you make a temporary table insert you known coumns with indexes. Then you loop through all tiem column and check if they doesn't exist in the temporary table and then add the index in an preprared statement after all is done delete the temporary table | |
| Apr 21, 2020 at 22:18 | history | edited | StixO | CC BY-SA 4.0 |
formatting code
|
| Apr 21, 2020 at 22:17 | comment | added | StixO | @Akina in this case it makes sense.. just for reference - consider the use case similar to: dba.stackexchange.com/questions/211801/… Check out your comments .on your answer .. however I am using the query like this one here as it is faster.. pastebin.com/guiYHkT1 | |
| Apr 21, 2020 at 22:06 | comment | added | StixO | I understand there are implications of indexes - these are historical data records and they get updated with new records and each new record has a new date.now() so the record is at the end. The data is queried based on the date range - not by Primary key or any other fields nothing else. So if you had a table where you insert datetime and then 2 other values and say give me x from date to date .. I just want this script so I can automate adding those indexes on all of these tables .. | |
| Apr 21, 2020 at 21:29 | comment | added | Jesus Uzcanga | Did you calculate the implication of running such a script to create all those indexes, I mean, each index will increase dramatically the tablespace file size, other than block some processes during the index creation. | |
| Apr 21, 2020 at 19:34 | comment | added | Akina | The goal here is to create indexes on all of these columns of type date, timestamp where no index currently exists In a half of cases the index by single field makes no sense. | |
| Apr 21, 2020 at 18:10 | history | asked | StixO | CC BY-SA 4.0 |