Recent

Author Topic: [SOLVED] Help with SQL  (Read 3098 times)

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Help with SQL
« Reply #15 on: March 25, 2021, 01:08:40 pm »
Do you have MySQL Workbench?
Maybe throw it at the Query-Analyzer.
Should show where the Bottleneck is
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

dseligo

  • Hero Member
  • *****
  • Posts: 1196
Re: Help with SQL
« Reply #16 on: March 25, 2021, 01:24:24 pm »
Do you have index on column fnTOPIC_IDX in table tnURL?
That would probably help.

paweld

  • Hero Member
  • *****
  • Posts: 970
Re: Help with SQL
« Reply #17 on: March 25, 2021, 01:45:42 pm »
try this:
Code: SQL  [Select][+][-]
  1. 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][+][-]
  1. ALTER TABLE `tnURLS` ADD INDEX `idx_fnTOPIC_IDX` (`fnTOPIC_IDX`) USING BTREE;
« Last Edit: March 25, 2021, 01:48:59 pm by paweld »
Best regards / Pozdrawiam
paweld

pcurtis

  • Hero Member
  • *****
  • Posts: 951
Re: Help with SQL
« Reply #18 on: March 25, 2021, 05:21:23 pm »
Thanks.

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

It now executes in 0.15 secs with the large tables.  :o :o

More tomorrow.

Windows 10 20H2
Laz 2.2.0
FPC 3.2.2

pcurtis

  • Hero Member
  • *****
  • Posts: 951
Re: [SOLVED] Help with SQL
« Reply #19 on: March 26, 2021, 09:19:48 am »
Works in production.

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

Thanks all.
Windows 10 20H2
Laz 2.2.0
FPC 3.2.2

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: [SOLVED] Help with SQL
« Reply #20 on: March 30, 2021, 03:25:55 pm »
Works in production.

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

Thanks all.

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

 :D :D :D :D :D :D :D ;D ;D ;D ;D ;D ;D ;D ;D
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018