Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Contents
Step 1: Test fulltext estimates.
|
Step 2: Correctness testing
|
Step 1: Test fulltext estimates.
MDEV-40777 includes code to estimate how many matches fulltext index for a keyword. That code needs testing.
Let's take this definition
CREATE TABLE mvi_ft_estimate_test ( |
id INT AUTO_INCREMENT PRIMARY KEY, |
attributes JSON,
|
attr1 VARCHAR(255), |
INDEX idx_attributes ((CAST(attributes->'$.tags' AS CHAR(32) ARRAY))), |
INDEX idx_attr1 (attr1) |
);
|
Insert, say 10M rows.
Generate dataset like this (first idea that came to mind):
-- de-duplicated list of names. We'll need it later on:
|
create table all_names ( |
name varchar(100) primary key |
);
|
set pop190_count = (select count * from pop1980);
|
for i in 1...10M do |
|
|
json='{"tags": []}'; |
// 0.01% probability of empty document: |
if (rand() < 1/100. /100) |
goto end; |
|
for (i=0;; i++) { |
// get a random row from pop1980 table; |
set @pk=pop190_count*rand(); |
// Get the name. This way, we get popular names more often, |
// rougly according to the distribution. |
set @name= (select firstname from pop1980 where pk=@pk);
|
|
// Save the name |
insert ignore into all_names values(@name);
|
|
|
// Insert the @name into "tags" array: |
json = ... ;
|
|
/* |
Not counting empty arrays, let the array have
|
50% = 1 element
|
25% = 2 elements
|
12.5% - 4 elements
|
etc.
|
*/
|
if (rand() > 0.5) |
break; |
}
|
insert into mvi_ft_estimate_test (attributes) values (json);
|
}
|
Then, shutdown and restart the server.
Then,
for each row in all_names { |
$name= <the name>;
|
analyze format=json
|
select *
|
from t1 force index (idx_attributes)
|
where json_overlaps(attributes->'$.tags', '"$name"'); |
Record rows and r_rows.
|
}
|
Then, plot rows vs r_rows.
And distribution of rows/r_rows (we only request values that exist to no divide-by-zero questions here).
Step 2: Correctness testing
Test that indexed reads produce correct results.
Construct two tables with identical content, one has an ARRAY index, another one doesn't. Try various datatypes for the ARRAY index (see the linked MDEV and/or mtr tests in the tree to see what is accepted).
Run various queries in form
select ... from tbl where cond ...
|
and the cond includes JSON_CONTAINS and/or JSON_OVERLAPS predicates that the optimizer will use to do index reads through ARRAY index.
Compare query results.
Attachments
Issue Links
- relates to
-
MDEV-40168 Indexes over JSON data based on Fulltext index
-
- In Progress
-
-
-
- Open
-