No aparece la columna de los ‘index’ cuando transcribo la tabla PANDAS al formulario Python

Tengo un problema para visualizar una tabla PANDAS en una Qtablewidget. Una tabla normal sí sale bien pero cuando uso la función PIVOT no salen la descripción de los ‘index’.

    conn = pymysql.connect(host='localhost', user='root', password='rincon', db='texpal')
    cur = conn.cursor()
    cur.execute('SELECT idpedido,idarticulo,idcolor,idtalla,cantidad FROM detalle_pedido')
    table_rows = cur.fetchall()
    df = pd.DataFrame(table_rows)
    dff=pd.pivot_table(df,index=(0,1,2),columns=3,values=4,fill_value=0)
    self.tableWidget_2.setColumnCount(len(dff.columns))
    self.tableWidget_2.setRowCount(len(dff.index))
    for i in range(len(dff.index)):
        for j in range(len(dff.columns)):
            self.tableWidget_2.setItem(i,j,QtWidgets.QTableWidgetItem(str(dff.iloc(i, j))))

La tabla me parece que está bien porque con PRINT salen los indices y los valores. Pero con QtableWidget solo aparecen los valores y en sus respectivas columnas. Pero no sale la columna con la descripción de los ‘index’.

mysql – Why there may be different delete-marked versions of a record in a unique secondary index

I have seen a comment in MySQL/InnoDB source code, however, I do not understand why there may be different delete-marked versions of a record in a unique secondary index? due to MVCC?

Note that in a unique secondary index there may be different delete-marked versions of a record where only the primary key values differ: thus in a secondary index we must use next-key locks when locking delete-marked records

oracle – Index not used in a remote query when values are supplied from a PL/SQL collection

I have a table with primary key column on a remote database:

create table tab (
  id primary key,
  pad
) nologging as
select level id, lpad('x', 800) pad
from dual
connect by level <= 100000;

If I query a few rows from remote table by id, over db link:

create type id_list is table of number;

declare
    type tab_list is table of u.tab@dblink%rowtype;

    v_result tab_list;
    v_id_list id_list := id_list(10003, 10004, 10005);
begin
    select *
    bulk collect into v_result
    from u.tab@dblink
    where id in (select value(i) from table(v_id_list) i);
end;

This simple query takes around 3 seconds to complete. This time is linearly dependent on the number of values in v_id_list collection. Each additional id increases query time by about ~1s.

Query plan on local database is:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |      1 |        |      3 |00:00:01.68 |       |       |          |
|   1 |  NESTED LOOPS                       |      |      1 |      1 |      3 |00:00:01.68 |       |       |          |
|   2 |   SORT UNIQUE                       |      |      1 |      3 |      3 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
|   3 |    COLLECTION ITERATOR PICKLER FETCH|      |      1 |      3 |      3 |00:00:00.01 |       |       |          |
|*  4 |   FILTER                            |      |      3 |      1 |      3 |00:00:02.69 |       |       |          |
|   5 |    REMOTE                           | TAB  |      3 |        |    300K|00:00:03.45 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

"   4 - filter(""ID""=VALUE(KOKBF$))"

The remote query was executed 3 times for a total of 300k rows (3 full table scans). Similarly, querying v$sql on
remote database reveals that the following query was executed 3 times:

SELECT /*+ */ "ID","PAD" FROM "U"."TAB" "TAB"

Why is the index not used in this case?

I verified, that the index is used correctly when values are supplied in pure SQL, even from another table:

create table ids nologging as
select 10003 id from dual union all
select 10004 from dual union all
select 10005 from dual;

select *
from u.tab@dblink
where id in (select id from ids);

I also checked that:

  • statistics were refreshed,
  • driving_site hint does not help.

I managed to get Oracle to use index eventually by extracting collection to subquery and adding materialize hint:

with ids AS (
  select /*+ materialize */ value(i)
  from table(v_id_list) i
)
select *
bulk collect into v_result
from u.tab@dblink
where id in (select i.id from ids i);

Is there a better way to tell optimizer to use index, without using explicit hints?

Tested on Oracle 12.1.0.2

postgresql – Why is my index not being used in this nested query?

I have a nested query: an inner geoquery to filter, then an outside query to get the ids of the objects.
Because this query became a speed bottleneck in an important operation, I am working on improving it.

This is where I stand:

  • I can’t understand why the query won’t use an index on the select column.
  • When I EXPLAIN the query, I see that it is using a Seq Scan where I expected it to use the index I created.

The table has 34k rows.

This query usually selects a couple hundreds of rows, but in this new particularly large case it selects 5k rows.

Original version

The query was:

  • Querying for objects within an area (ST_WITHIN);
  • Then selecting the parent_id of the object;
  • Then selecting all the objects whose id or parent_id were in the list.

The complicated part is that the table has composite objects, that are “linear objects” ¹ made up of child objects. And if some of their child objects are in the area, we want to select the parent object and all the siblings.

¹ (Lines, pipes, etc; bBézier curves built by using several child objects as… nodes.)

SELECT id
FROM public.world_objects
-- where the root_id is in...
WHERE COALESCE(parent_id, id) IN (
    -- select the root_id: parent_id, or the id if no parent_id.
    SELECT COALESCE(parent_id, id)
    FROM public.world_objects
    WHERE is_deleted = false
        -- COALESCE to '' because we cannot compare NULL using an equals sign.
        AND COALESCE(public.world_objects.label_type, '') = COALESCE('80624e39-96b8-4058-a61e-5171060105a6', '')
        AND ST_DWithin(
            ST_SetSRID(st_point(-12.3456788012345, 12.3456788012345), 4326),
            ST_SetSRID(st_point(longitude, latitude), 4326),
            100000, false)
        AND internal_type_description NOT IN ('Chain', 'Flightpath', 'Line', 'Pipe'));

Current version

To speed this process up, we are computing and persisting COALESCE(parent_id, id) into root_id.
A trigger before insert or update sets the value.

(We can’t use a generated column because that’s only for pg12+, and we have a server that we can’t yet upgrade from pg10.)

So we changed the query to:

SELECT id
FROM public.world_objects
WHERE root_id IN (
    SELECT root_id
    FROM public.world_objects
    WHERE is_deleted = false
        -- COALESCE to '' because we cannot compare NULL using an equals sign.
        AND COALESCE(public.world_objects.label_type, '') = COALESCE('80624e39-96b8-4058-a61e-5171060105a6', '')
        AND ST_DWithin(
            ST_SetSRID(st_point(-12.3456788012345, 12.3456788012345), 4326),
            ST_SetSRID(st_point(longitude, latitude), 4326),
            100000, false)
        AND internal_type_description NOT IN ('Chain', 'Flightpath', 'Line', 'Pipe'));

This sped the process up from 1m:20s (!!) to 0m:10s.
But that’s still a lot.

I am aiming for a magnitude not above 0m:0.01s = 100ms.
I think that this must be feasible, for a table with less than 100k rows. Surely I am doing something wrong.

When the improved query is EXPLAINed, the weight seems to be on joining the inner query with the outer query:

the EXPLAIN of the improved query

I added an index on the root_id, but it does not seem to be used?!?

CREATE INDEX "world_objects_IX_root_id" ON public.world_objects USING btree (root_id)

What’s worse is that a co-worker can speed the query up by running vacuum after adding the index.
But not me in my machine, following exactly the same steps.

We have the same PG version, but different PostGIS versions:

  • PostgreSQL 12.5, compiled by Visual C++ build 1914, 64-bit
  • PostGIS
    • I have POSTGIS="3.1.0 3.1.0" (EXTENSION) PGSQL="120" GEOS="3.9.0-CAPI-1.14.1" PROJ="7.1.1" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)"
    • and they have 3.0.1

But the inner query is fast, so I don’t think that the different PostGIS version is the source of the problem.

  • Shouldn’t the root_id index be used in the query?
  • Are we doing something wrong? What should be done differently? 🙁
  • Is this behaviour explainable?

java – Index of bounds determinar si una matriz es cuadrada o no

Tengo este algoritmo para determinar si una matriz es símetrica o no, cuando la hago simétrica me retorna el valor esperado pero cuando la hago asimétrica para ver si me retorna el valor esperado me da index of bounds. adjunto mi código

public boolean symmetricMatrix (int matrix()()){
    for(int i = 1; i < matrix.length; i++){
      for(int j = 0; j < i; j++){
        if(matrix(i)(j) != matrix(j)(i))
          return false;
      } // For j
    } // For i
    return true;
  } // Fin de método 

magento2 – Magento 2: How to redirect to current store url with parameters in url from `controller_action_postdispatch` observer [Google search index issue]

When I enter my site’s internal URL for example- xyz.com/women/tops
It redirects me to the home page xyz.com and also shows the error in
Google Search Console showing URL is not available to Google as
shown in the screenshot.

What I want is My Url should be redirected to the current store URL(not base URL) and also not show an error in Google Search Console.

Screenshot1

I’m redirecting to a different store following way which is not the correct way to redirect to store Url, Also it’s redirected as expected but shows the error in google search

$storeId = 1;
$urlInterface = MagentoFrameworkAppObjectManager::getInstance()->get('MagentoFrameworkUrlInterface');
$redirecturl = $urlInterface->getCurrentUrl();
$redirecturl = str_replace($this->storeManager->getStore()->getBaseUrl(), '', $redirecturl);
$redirecturl = explode('?', $redirecturl);
$store=$this->storeManager->getStore($storeId);
$observer->getControllerAction()->getResponse()->setRedirect($store->getBaseUrl().redirecturl(0)."?geoipss_redirect=1&gipss_store_id=".$storeId); //redirecturl(0) == 'women/tops'
return $this;

I have tried adding a static URL that works fine in setRedirect()
but I need to redirect to a specific store’s current URL(not base
URL).

I have also tried with
$this->storeManager->getStore()->getCurrentUrl() it shows error in
google search.

Let me know If anyone has any idea.

Are page numbers of a book a good analogy for a clustered index?

The title pretty much covers the question.

I think that the index of a book is a good analogy for a non-clustered index, as it demonstrates the extra storage and physical separation properties of a non-clustered index. I also think that the page numbers of a book represent well the physical ordering of data, similar to the structure of data with a clustered index.

However I’m a bit of a newbie to more advanced database theory. Does the analogy for page numbering make sense for a clustered index, or are there any properties of a clustered index where this analogy falls over?

index – How do I add composite MySQL Indexes with denormalization and JOINS?

I have the following tables:

CREATE TABLE base_event (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_by ... -- some columns
);

CREATE TABLE transaction_events (
    event_id BIGINT UNSIGNED NOT NULL,
    transaction_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    merchant_id BIGINT UNSIGNED NULL DEFAULT NULL,   
    merchant_city VARCHAR (...) NULL DEFAULT NULL, -- Denormalize
    customer_id BIGINT UNSIGNED NULL DEFAULT NULL,
    customer_ip_address VARCHAR(...) NULL DEFAULT NULL, -- Denormalize
    ...
    FOREIGN KEY (event_id) REFERENCES base_event(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id),
);

CREATE TABLE customers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    customer_ip_address VARCHAR(...) NULL DEFAULT NULL,
    ...
);

CREATE TABLE merchants (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ...
);

And my SELECT:

(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.customer_ip_address = 'abc' AND t.transaction_time > 'abc')
UNION DISTINCT
(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.merchant_city = 'abc' AND t.transaction_time > 'abc')

And my indexes are:

ALTER TABLE transaction_events 
ADD INDEX index_1 (customer_ip_address, transaction_time),
ADD INDEX index_2 (merchant_city, transaction_time);
  1. My query is in this form to avoid OR.
  2. I’ve denormalized to a degree for the sake of the indexes.
  3. I do not need to reference my base_event table for this query.
  4. The relation with transaction_events to customers and merchants is not 1-to-1 but 1-to-0-or-1.

My questions:

  1. I can get rid of the wildcard, but transaction_events has around 20 columns (Would it help creating any further indexes to speed up the query?
  2. Do I need to put any other composite indexes (that potentially reference my FKs) to further improve this query?