Recent

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

Zvoni

  • Hero Member
  • *****
  • Posts: 544
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 IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

dseligo

  • Full Member
  • ***
  • Posts: 231
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

  • Sr. Member
  • ****
  • Posts: 273
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
paweld

pcurtis

  • Hero Member
  • *****
  • Posts: 532
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
Laz 2.0.10
FPC 3.2.0

pcurtis

  • Hero Member
  • *****
  • Posts: 532
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
Laz 2.0.10
FPC 3.2.0

Zvoni

  • Hero Member
  • *****
  • Posts: 544
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 IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

 

TinyPortal © 2005-2018