[SOLVED] Help with SQL
lazarus / 16-Databases

----------------------------------------------------------------------
pcurtis · 2021-03-24 09:53

I know this isn't about FPC / LAZ, but where can I get help with
creating  a SQL (MariaDB) query?

Thanks in advance.

----------------------------------------------------------------------
Zvoni · 2021-03-24 09:58

SQLFiddle? Beware: MySQL 5.6 only (no idea which Version of MariaDB
that would be)
http://sqlfiddle.com/

DB-Fiddle
https://www.db-fiddle.com/

OTOH, if you don't get it, just post it here (if it's acceptable with
Forum-policy), and we'll try to help.
I'm doing that in other forums i'm a member of for some years now, and
noone ever complained, since other people can learn something from it

----------------------------------------------------------------------
pcurtis · 2021-03-24 10:13

Thanks. I'll have a look. I will also post my issue

I would like to update tnTOPICS and set fnCOUNT = number rows returned
from tnURLS only where tnTOPIC.fnIDX = tnURLS.fnTOPIC_IDX and
fnGOTDATA = 0

CREATE TABLE `tnTOPICS` (  
  `fnIDX` int(11) NOT NULL AUTO_INCREMENT, 
  `fnDESCRIPTION` varchar(50) DEFAULT '', 
  `fnCOUNT` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`fnIDX`)
) ENGINE=InnoDB;

INSERT INTO `tnTOPICS` 
(`fnIDX`, `fnDESCRIPTION`, `fnCOUNT`) 
VALUES 
(1, 'TOPIC1', 0), 
(2, 'TOPIC2', 0), 
(3, 'TOPIC3', 0), 
(4, 'TOPIC4', 7), 
(5, 'TOPIC5', 3); 

CREATE TABLE `tnURLS` ( 
`fnIDX` int(11) NOT NULL AUTO_INCREMENT, 
`fnTOPIC_IDX` int(11) NOT NULL DEFAULT 0, 
`fnGOTDATA` tinyint(4) NOT NULL DEFAULT 0, 
PRIMARY KEY (`fnIDX`)
) ENGINE=InnoDB;

INSERT INTO `tnURLS` 
(`fnIDX`, `fnTOPIC_IDX`, `fnGOTDATA`) 
VALUES 
(1, 1, 1), 
(2, 1, 0), 
(3, 1, 0), 
(4, 2, 1), 
(5, 2, 1), 
(6, 2, 1), 
(7, 3, 0), 
(8, 3, 0), 
(9, 3, 0), 
(10, 1, 1), 
(11, 2, 1), 
(12, 3, 1);

Table tnTOPICS should look like this after the update

1 TOPIC1 2 
2 TOPIC2 4 
3 TOPIC3 1 
4 TOPIC4 0 
5 TOPIC5 3

Thanks for your help.

----------------------------------------------------------------------
Zvoni · 2021-03-24 10:28

and fnGOTDATA = 0 doesn't make sense
You expect for Topic2 to return 4, but all Topic2 in tnURLS has
fnGOTDATA = 1, not 0

i think it should be !...and fnGOTDATA=1"

EDIT: Your expected result for Topic4 to be 0 also doesn't make sense
(compared to Topic5), since you don't have any Detail in tnURLS for
Topic4
Should expect Topic4 7 as result

1 TOPIC1 2
2 TOPIC2 4
3 TOPIC3 1
4 TOPIC4 7
5 TOPIC5 3

----------------------------------------------------------------------
Zvoni · 2021-03-24 10:44

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
DISTINCT T1.fnIDX, T1.fnDescription,(CASE WHEN T2.fnTOPIC_IDX IS NULL
THEN T1.fnCOUNT ELSE Count(T2.fnIDX) OVER(PARTITION BY T2.fnTOPIC_IDX)
END) As fnCountFROM tnTopics T1LEFT JOIN tnURLS T2 ON
T2.fnTOPIC_IDX=T1.fnIDX AND T2.fnGOTDATA=1ORDER BY T1.fnIDX 
Returns:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i fnIDX  
fnDESCRIPTION   fnCount1       TOPIC1              22       TOPIC2   
         43       TOPIC3              14       TOPIC4              75
     TOPIC5              3
Tried with SQLite, but i don't see anything specific which wouldn't
work for MySQL/MariaDB

EDIT: Or is it the other way round? Are you expecting TOPIC4 and
TOPIC5 to be set to 0?
If yes, then Line 4 of the SQL should be
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Count(T2.fnIDX) OVER(PARTITION BY T2.fnTOPIC_IDX) As fnCount would
return:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i fnIDX  
fnDESCRIPTION   fnCount1       TOPIC1              22       TOPIC2   
         43       TOPIC3              14       TOPIC4              05
     TOPIC5              0
EDIT2: Just saw your Signature.....
Laz 2.10.0 ???? 
Are our boys (and girls?) withholding the newest Lazarus-Version from
us?

----------------------------------------------------------------------
pcurtis · 2021-03-24 12:36

Thanks so much. Now the work can begin.

----------------------------------------------------------------------
Zvoni · 2021-03-24 12:45

Please note, that this is only the SELECT-Part of the SQL

You want to UPDATE tnTOPICS Column fnCOUNT
so you would need an UPDATE-Statement, but i hope you know how to go
from here

Should be something like (Aircode)
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE
A1SET A1.fnCOUNT=A2.fnCOUNT FROM tnTOPICS As A1,(HERE GOES THE ABOVE
SELECT-STATEMENT) As A2WHERE A1.fnIDX=A2.fnIDX

----------------------------------------------------------------------
pcurtis · 2021-03-25 05:01

OK. I've tried this

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE
A1SET A1.fnCOUNT = A2.fnCOUNTFROM  tnTOPICS AS A1,  (SELECT DISTINCT 
T1.fnIDX,  T1.fnDESCRIPTION,  (CASE WHEN T2.fnTOPIC_IDX IS NULL THEN
T1.fnCOUNT ELSE Count(T2.fnIDX) OVER(PARTITION BY T2.fnTOPIC_IDX) END)
As fnCOUNT  FROM    tnTOPICS T1  LEFT JOIN    tnURLS T2  ON   
T2.fnTOPIC_IDX = T1.fnIDX AND    T2.fnGOTDATA = 0  ORDER BY   
T1.fnIDX) AS A2WHERE  A1.fnIDX = A2.fnIDX 
and it doesn't work.

It complains of a syntax error

Quote/* SQL Error (1064): You have an error in your SQL syntax; check
the manual that corresponds to your MariaDB server version for the
right syntax to use near 'FROM
  tnTOPICS AS A1,
  (SELECT DISTINCT
  T1.fnIDX,
  T1.fnDESCRIPTION,...' at line 3 */
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1
query: 0.000 sec. */

 The SELECT statement works fine on it's own.

Any ideas?

----------------------------------------------------------------------
Zvoni · 2021-03-25 07:19

Damn! I've seen that error a lot.

Found this:
https://stackoverflow.com/questions/10262300/syntax-error-near-from-
when-using-update-with-join-in-mysql
Looks like you have to join first, and then SET the new Value

----------------------------------------------------------------------
pcurtis · 2021-03-25 07:25

I hate JOIN's

I think I'm close with this, and JOIN free 

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE
tnTOPIC SET tnTOPIC.fnCOUNT = (  SELECT DISTINCT  COUNT(tnURL.fnIDX)
OVER (PARTITION BY tnURL.fnTOPIC_IDX)FROM  tnURLWHERE 
tnURL.fnTOPIC_IDX = tnTOPIC.fnIDXAND  tnURL.fnGOTIMAGE = 0    )WHERE 
fnCOUNT > 0

----------------------------------------------------------------------
Zvoni · 2021-03-25 07:55

be careful!
your WHERE fnCount>0 filters to the original data in tnTOPICS, not the
Count from the inner Query.
So you would set only those records which have fnCount>0 before
counting the URL's

EDIT
After thinking about it:
Have you thought to make a View out of the Inner Query?
The Inner query is static (no dynamic filters), so you could address
it as a regular table

----------------------------------------------------------------------
pcurtis · 2021-03-25 08:47

You've lost me. I haven't got a clue about views and such.

----------------------------------------------------------------------
pcurtis · 2021-03-25 10:18

OK, I bit the bullet and tried this

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE
tnTOPIC AS T1INNER JOIN tnURL AS T2 ON T1.fnIDX = T2.fnTOPIC_IDXSET
T1.fnCOUNT = (                  SELECT DISTINCT                   
COUNT(tnURL.fnIDX) OVER (PARTITION BY tnURL.fnTOPIC_IDX)             
   FROM                    tnURL                  WHERE              
    tnURL.fnTOPIC_IDX = T1.fnIDX                  AND                
  tnURL.fnGOTIMAGE = 0                    ) 
Any comments / notes?

----------------------------------------------------------------------
Zvoni · 2021-03-25 11:04

Looks exactly the way it's supposed to work with MySQL

----------------------------------------------------------------------
pcurtis · 2021-03-25 11:41

When I execute it on small tables (tnTOPICS = 5 records, tnURLS = 13
records) it execute fast, 0.15 secs.
When I try it on real data (tnTOPICS = 30000 records, tnURLS = 250000
records) it didn't finish after 20 minutes?

Both tables have primary indexes.

----------------------------------------------------------------------
Zvoni · 2021-03-25 12:08

Do you have MySQL Workbench?
Maybe throw it at the Query-Analyzer.
Should show where the Bottleneck is

----------------------------------------------------------------------
dseligo · 2021-03-25 12:24

Do you have index on column fnTOPIC_IDX in table tnURL?
That would probably help.

----------------------------------------------------------------------
paweld · 2021-03-25 12:45

try this:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE
tnTOPICS A1 INNER JOIN (SELECT fnTOPIC_IDX fnIDX, COUNT(1) fnCOUNT
FROM tnURLS WHERE fnGOTDATA=1 GROUP BY fnTOPIC_IDX) A2 ON (A1.fnIDX =
A2.fnIDX) SET A1.fnCOUNT = A2.fnCOUNT and as @desligo say: add index:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ALTER TABLE
`tnURLS` ADD INDEX `idx_fnTOPIC_IDX` (`fnTOPIC_IDX`) USING BTREE;

----------------------------------------------------------------------
pcurtis · 2021-03-25 16:21

Thanks.

Looking good.
I added the index and changed the script.

It now executes in 0.15 secs with the large tables. 

More tomorrow.

----------------------------------------------------------------------
pcurtis · 2021-03-26 08:19

Works in production.

Now I'm not sure who's a mans best friend. A dog or index? 

Thanks all.

----------------------------------------------------------------------
Zvoni · 2021-03-30 13:25

Quote from: pcurtis on March 26, 2021, 09:19:48 amWorks in production.

Now I'm not sure who's a mans best friend. A dog or index? 

Thanks all.

QuoteA query walks into a bar, sees two tables and asks, "do you mind
if I join you?"