Tag: columns

Indexed columns in SQL Server do not appear to work for basic queries according to execution plan

Disclaimer: I’m not a DBA. I have picked up a few things from this board in the past that I’m building from.

I have a table of google analytics session start times. I have an index on each column. I want to filter for all sessions that were started between two dates. Screenshot below shows the query, and the index.

Query text and index properties

The query runs quickly but I do not believe it’s using the index based on the Execution plan which both says that there’s a missing index and shows a table scan rather than an index scan:

execution

Why?

Is it because of something about the way I’m searching through the datetime? If instead of looking between dates, I set it equal to a date, the execution plan shows it using the index:

Using index

But it’s not just this table or datetime. Here’s a different table with an index on a varchar column:

metadata index

And a simple query on this one also tells me I’m missing the index:

missing md index

I’m stumped.

Prevent block editor from adding overlap by default to columns

When I create a new columns block, the editor adds the overlap option by default, so the class “is-style-twentytwentyone-columns-overlap” gets added.

I do not want this, but I can’t seem to get rid of it although the class is not in my inc/block-patterns.php file.

I’m using a created child theme for Twenty Twenty One theme.

I also tried to unregister the block style with custom js file:

wp.domReady(() => {     wp.blocks.unregisterBlockStyle('core/columns', 'twentytwentyone-columns-overlap'); } );

Here I can not see the option anymore to choose it, but when creating new columns it’s still added by default.

Any idea on how to get the columns without overlap by default please?

How to do a ranked search based on number of multiple columns matched


Context

I am trying to create a ranked-customer-search that will order results based on "most likely correct". We have several factors we search by, but to keep it simple I will stick with just name, phone number, & email. The goal is that if the customer has an existing account, we use that instead of creating a new account.

It is also worth noting that for this system, a customer account is US state-specific. So it is technically possible for a single person to have 49 existing accounts and still need to have a "new account" created, so there are often many duplicate accounts we can copy information from.

My Attempt

The query below uses binary values to determine the rank of a result. So a match based on a phone number (0100) scores higher than a match based on name (0001) or email (0010). This works pretty well but isn’t quite ideal. For example, an account that matches both email & name will rank lower than an account that matches only a phone number. Unfortunately, this is pretty much my limit when it comes to creating queries.

(The UNIONs below are required because they allow better indexes to be used)

SELECT     results.id,     SUM(results.score) AS total_score FROM (     (         SELECT             c1.id,             4 AS score         FROM customers c1         WHERE             c1.phone = :phone     ) UNION ALL (         SELECT             c2.id,             2 AS score         FROM customers c2         WHERE             c2.email = :email     ) UNION ALL (         SELECT             c3.id,             1 AS score         FROM customers c3         WHERE             c3.first_name = :first_name             AND c3.last_name = :last_name     ) ) results GROUP BY     results.id ORDER BY     total_score DESC LIMIT 10;

My Question

I am not sure how to change it so that matching multiple less-important factors ranks higher than a single important factor?

Also, since this is my first time creating a search query like this, there is likely a better and/or more standard way of doing this; so any resources you can share related to this would also be greatly appreciated!

Group by Multiple columns and then count different value of same column

I want to achieve the result which tells me the number of males and females of each disability types in each district. each district can have multiple disabilities.

So far i have reached the following query :

  SELECT    DistrictId,    fb.DisabilityTypeId,    SUM(     CASE WHEN GenderId = 1 THEN 1 ELSE 0 END   ) AS Male,    SUM(     CASE WHEN GenderId = 2 THEN 1 ELSE 0 END   ) AS Female  FROM    Districts d    LEFT OUTER JOIN FormAddresses a ON d.Id = a.DistrictId    INNER JOIN PeopleForms pf ON a.PeopleFormId = pf.Id    INNER JOIN FormBeneficiaries fb ON pf.Id = fb.PeopleFormId    INNER JOIN FormPersonalInfos fp ON pf.Id = fp.PeopleFormId  where    a.IsDeleted = 0    AND pf.FormTypeId = 2    AND d.CityId = 3  GROUP BY    DistrictId,    fp.GenderId,   fb.DisabilityTypeId

which gives the following result :

DistrictId  |   DisabilityTypeId    |   Male  | Female     1       |       2               |   1     |     0     3       |       2               |   0     |     3     5       |       16              |   1     |     0     5       |       20              |   2     |     0     5       |       20              |   0     |     1

But i want to achieve the following result :

DistrictId  |   DisabilityTypeId    |   Male  | Female     1       |       2               |   1     |     3     5       |       16              |   1     |     0     5       |       20              |   2     |     1

i somehow managed to get the expected result, but that with some complex sub-queries in the select clause of each gender which i didnt like and was not sure about the performance.

how can i write an efficient query for the desired result that i want?

Thanks.

Random Hyperlinks Not Working in Nested Columns with Lists

I am new here and have beginner level experience with WordPress so please bear with me if I am leaving out any pertinent information.

Towards the lower portion of this https://www.logicmanager.com/ca-solutions-menu/ landing page, I have a series of containers with columns and nested columns. Within these nested columns I have included hyperlinked text to list items. Ideally, all of the hyperlinked list items would click out to their respective landing pages.

However, for some reason unbeknownst to me, most of the hyperlinks are not working properly and the text items are un-clickable.

Does anyone know why this might be and how I can fix it? Are there any additional details I can provide to help resolve this issue? Is this forum even meant for questions like this?? Many thanks in advance!

Mysql: Any way to properly index 3 ENUM columns with the same options ? (A OR B OR C)

I have 3 enum() columns with the same option values inside.
I first tried to use the "set" datatype which originally was meant to do that (hold multiple values from a set) but it seems that datatype isn’t managed for 15+ years and isn’t even supporting an index.
Is there a nice way to index those 3 columns so I can use them in searched without destroying query performance ?

SELECT * FROM TABLE WHERE a=’x’ OR b=’x’ OR c=’x’

I thought about creating a virtual field which uses a boolean logic (&) on the 3 enum field-numbers and combines them into a large number but that’s quite a hack and not nice to maintain.

Has someone solved this sort of task elegantly ? (I do not want to use a support table and JOIN it, I want to stay with a single table)

Storing timeseries data with dynamic number of columns and rows to a suitable database

I have a timeseries pandas dataframe which dynamically increases the columns every minute as well as adds a new row:

Initial:

timestamp                100     200     300 2020-11-01 12:00:00       4       3       5

Next minute:

timestamp                100     200     300   500 2020-11-01 12:00:00       4       3       5     0 2020-11-01 12:01:00      14       3       5     4

The dataframe has these updated values and so on every minute.

so ideally, I want to design a database solution that supports such a dynamic column structure. The number of columns could grow to over 20-30k+ and since it’s one minute timeseries, it will have 500k+ rows per year.

I’ve read that relational db’s have a limit on the number of columns so that might not work here, but also, since I am setting the data for new columns and assigning a default value(0) to previous timestamps, I lose out on the DEFAULT param that’s there on MySQL.

Eventually, I will be querying data for 1 day, 1 month to get the data for the columns and their values.

Please suggest a suitable database solution for this type of dynamic row and column data.

Flexible table columns width, rows independent?

Hello,

i have HTML table with 3 rows.
the third row contains in the middle three columns that contains extraordinary long phrasesphrases which cause columns above these long columns to be long too and that looks bad, because above rows contains images so spacing is different.

How can i set the HTML table columns width to be flexible, i mean so the wide third row columns does not influence width of the columns in other rows of the same table?…

Flexible table columns width, rows independent?

How can I make a best fit curve from a dataset from specific data columns in order to display it with their respective points?

I’ve got the next dataset:

 ArstreYSust = Dataset[{ <|"Velocidad [m/s]" -> 8,   "Fuerza de arrastre \[Theta]=2\[Degree] SIN FLAP [N]" -> 0.06,   "Fuerza de sustentación \[Theta]=2\[Degree] SIN FLAP [N]" ->    0.19, "Fuerza de arrastre \[Theta]=14\[Degree] SIN FLAP [N]" ->    0.14, "Fuerza de sustentación \[Theta]=14\[Degree] SIN FLAP \   [N]" -> 0.38,   "Fuerza de arrastre \[Theta]=20\[Degree] SIN FLAP [N]" -> 0.21,   "Fuerza de sustentación \[Theta]=20\[Degree] SIN FLAP [N]" ->    0.4, "Fuerza de arrastre \[Theta]=8\[Degree] CON FLAP [N]" ->    0.24, "Fuerza de sustentación \[Theta]=8\[Degree] CON FLAP [N]" \   -> 0.75|>,   <|"Velocidad [m/s]" -> 10,    "Fuerza de arrastre \[Theta]=2\[Degree] SIN FLAP [N]" -> 0.08,    "Fuerza de sustentación \[Theta]=2\[Degree] SIN FLAP [N]" ->    0.31, "Fuerza de arrastre \[Theta]=14\[Degree] SIN FLAP [N]" ->    0.2, "Fuerza de sustentación \[Theta]=14\[Degree] SIN FLAP [N]" \   -> 0.5, "Fuerza de arrastre \[Theta]=20\[Degree] SIN FLAP [N]" -> 0.3,   "Fuerza de sustentación \[Theta]=20\[Degree] SIN FLAP [N]" ->    0.68, "Fuerza de arrastre \[Theta]=8\[Degree] CON FLAP [N]" ->    0.35, "Fuerza de sustentación \[Theta]=8\[Degree] CON FLAP [N]" \   -> 1.36|>, <|"Velocidad [m/s]" -> 12,   "Fuerza de arrastre \[Theta]=2\[Degree] SIN FLAP [N]" -> 0.11,   "Fuerza de sustentación \[Theta]=2\[Degree] SIN FLAP [N]" ->    0.49, "Fuerza de arrastre \[Theta]=14\[Degree] SIN FLAP [N]" ->    0.28, "Fuerza de sustentación \[Theta]=14\[Degree] SIN FLAP \  [N]" -> 0.79,   "Fuerza de arrastre \[Theta]=20\[Degree] SIN FLAP [N]" -> 0.46,   "Fuerza de sustentación \[Theta]=20\[Degree] SIN FLAP [N]" ->    0.86, "Fuerza de arrastre \[Theta]=8\[Degree] CON FLAP [N]" ->    0.44, "Fuerza de sustentación \[Theta]=8\[Degree] CON FLAP [N]" \  -> 1.96|>, <|"Velocidad [m/s]" -> 14,   "Fuerza de arrastre \[Theta]=2\[Degree] SIN FLAP [N]" -> 0.15,   "Fuerza de sustentación \[Theta]=2\[Degree] SIN FLAP [N]" ->    0.72,   "Fuerza de arrastre \[Theta]=14\[Degree] SIN FLAP [N]" -> 0.4,   "Fuerza de sustentación \[Theta]=14\[Degree] SIN FLAP [N]" ->    1.12, "Fuerza de arrastre \[Theta]=20\[Degree] SIN FLAP [N]" ->    0.69, "Fuerza de sustentación \[Theta]=20\[Degree] SIN FLAP \  [N]" -> 1.32,   "Fuerza de arrastre \[Theta]=8\[Degree] CON FLAP [N]" -> 0.6,   "Fuerza de sustentación \[Theta]=8\[Degree] CON FLAP [N]" ->    3.17|>, <|"Velocidad [m/s]" -> 16,   "Fuerza de arrastre \[Theta]=2\[Degree] SIN FLAP [N]" -> 0.18,   "Fuerza de sustentación \[Theta]=2\[Degree] SIN FLAP [N]" -> 0.8,   "Fuerza de arrastre \[Theta]=14\[Degree] SIN FLAP [N]" -> 0.45,   "Fuerza de sustentación \[Theta]=14\[Degree] SIN FLAP [N]" ->    1.29, "Fuerza de arrastre \[Theta]=20\[Degree] SIN FLAP [N]" ->    0.78, "Fuerza de sustentación \[Theta]=20\[Degree] SIN FLAP \  [N]" -> 1.53,   "Fuerza de arrastre \[Theta]=8\[Degree] CON FLAP [N]" -> 0.71,   "Fuerza de sustentación \[Theta]=8\[Degree] CON FLAP [N]" ->    3.48|>}];

I’ve had already build my list plot.

BUt I don’t know how to build a fit curve directly from my dataset in order to include it in the show command. I need to do it this way for different columns.

Thanks for reading and for your help.