Retuner kun en linie til brug ved joints

Altså, som jeg læser spørgsmålet, så kan det gøres ca. sådan (uden dog at have tjekket det under MySQL:

SELECT id,qda_d.number, Time as Timestamp,MAKETIME(TimeHours,TimeMin,0)as time, Condcode, DialedNum, qda_d.type ,qda_d.name,qda_d.description, MAKETIME(DurHours,DurMin,(Dur10ofMin*0.6)*10) as Duration
FROM col_data_test c, qos_digit_all qda_d
WHERE DialedNum LIKE CONCAT(qda_d.number,'%')
  AND c.prefix = SUBSTRING(DialedNum, 1, LENGTH(c.prefix))
  AND c.prefix = (SELECT MAX(c1.prefix) FROM col_data_test c1, qos_digit_all q WHERE c1.prefix = SUBSTRING(q.number, 1, LENGTH(c1.prefix)) AND q.DialedNum = qda_d.DialedNum)
order by id

Det kræver dog, at du er på mindst version 4.1, da den gør brug af en subselect.

Jeg er ikke helt sikkert på hvad du mener col_data_test indholder ikke prefix

FROM col_data_test {c}, qos_digit_all qda_d
WHERE DialedNum LIKE CONCAT(qda_d.number,'%')
AND {c}.prefix = SUBSTRING(DialedNum, 1, LENGTH(c.prefix)).

kolonerne DialedNum og CallingNum er egentlig af samme type, de repræsenter et telefon nr der kan være lokalt f.eks 6337 eller eksternt 077121212. Ud fra disse numre vil jeg gerne have den knyttet med den information som jeg har i viewet qos_digit_all. Jeg ville gerne høre hvorledes det ser ud med afvikling af denne joint /sub select. jeg har under mine test fundet at en forspørgelse tager ca. 5 sek. med
5000 col_data_test
1600 qos_digit_all der er et view med joint af 3 mindre tabeler.

col_data_test kan i fremtiden vokse til 100x størrelsen i dag eller mere. Er det rigtig det jeg gør? Eller hvad 

PS. jeg kører version 5 af MySQL

Ups, ja, jeg har nok ikke kigget nok på dit foregående spørgsmål, hvor prefix tabellen er specificeret. Jeg blev ledt lidt på vildspor af, at du opførte tabellen i spørgsmålet, men ikke inkluderede den i din forespørgsel. Samtidig har jeg heller ikke kigget nok på din tabeldefinition for col_data_test. Dit spørgsmål omtaler prefix, men din forespørgsel inkluderer den ikke. Måske er det det der har forvirret mig. Jeg troede din første tabel i forespørgslen (col_data_test) var din prefix tabel og den anden tabel (qos_digit_all) var den tabel du viste i spørgsmålet (jeg overså åbenbart, at det var den der hed col_data_test. Tja, ærlig talt ved jeg ikke rigtigt hvordan jeg skal forklare mine overvejelser, for lige nu ser de lidt mærkelige ud, det indrømmer jeg.

Men jeg vil gerne gøre et nyt forsøg. Det ville dog unægtelig gøre det nemmere, hvis jeg kunne få et vide, hvad tabellen qos_digit_all indeholder og hvordan den ser ud. Den mangler for at fuldende billedet.

BTW, du har brugt udtrykket

DialedNum LIKE CONCAT(qda_d.number,'%')

som omskrevet til den syntax jeg har brugt i min forespørgsel ville lyde

qda_d.number = SUBSTRING(DialedNum, 1, LENGTH(qda_d.number))

Det er egentlig bare to måder at skrive den samme ting. Jeg har ikke testet, om den ene måde er bedre end den anden.

Du har vist hvordan et view ved navn 'qos_digit_view' ser ud. Det view du bruger i din forespørgsel er imidlertid 'qos_digit_all'. Er feltet prefix heri omdøbt til number, i forhold til det viste view?

Ja det har jeg det beklager jeg meget.

her er dif. på qos_digit_all

/*Column Information For - cdr.qos_digit_all*/
----------------------------------------------

Field        Type          Collation        Null    Key    Default  Extra  Privileges                      Comment
-----------  -------------  ----------------  ------  ------  -------  ------  -------------------------------  -------
type        varchar(6)    utf8_general_ci  NO                              select,insert,update,references       
number      varbinary(43)  (NULL)            YES            (NULL)          select,insert,update,references       
name        varchar(45)    latin1_danish_ci  NO                              select,insert,update,references       
description  varchar(80)    latin1_danish_ci  NO                              select,insert,update,references       

/*Index Information For - cdr.qos_digit_all*/
---------------------------------------------

Table  Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment
------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------


/*DDL Information For - cdr.qos_digit_all*/
-------------------------------------------

View          Create View                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
-------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qos_digit_all  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `qos_digit_all` AS select _utf8'local' AS `type`,`qos_users`.`extn` AS `number`,`qos_users`.`name` AS `name`,`qos_users`.`fullname` AS `description` from `qos_users` union select _utf8'global' AS `global`,concat((select `qos_config`.`dialout` AS `dialout` from `qos_config`),`qos_digit_view`.`prefix`) AS `prefix`,`qos_digit_view`.`provider` AS `provider`,`qos_digit_view`.`service` AS `service` from `qos_digit_view`

Okay, så er her et nyt bidrag:


SELECT id,q.number, Time as Timestamp,MAKETIME(TimeHours,TimeMin,0)as time, Condcode,
      DialedNum, q.type ,q.name,q.description,
      MAKETIME(DurHours,DurMin,(Dur10ofMin*0.6)*10) as Duration
FROM col_data_test c,  qos_digit_all q
WHERE DialedNum LIKE CONCAT(q.number,'%')
AND  q.number = (SELECT MAX(number)
                      FROM FROM qos_digit_all
                      WHERE c.DialedNum LIKE CONCAT(number, '%'))
order by id

Hej igen mange tak for dit bidrag, det virker fint en forspørgelse tager ca. 5-6 sekunder for;

Men hvis jeg prøver at køre en join til for at CalledNum (på samme måde som DialedNum) sker det uheldige at maskinen går helt amok, og bruger 100% processor tid, jeg kan end ikke skifte mellem vinduer. jeg prøvet først at lukke klienten ned men det hjalp ikke (tog maskinen godt en time). Jeg lukket derefter selve serveren ned. forspørgelsen jeg benyttet var:

SELECT id,q.number, Time as Timestamp,MAKETIME(TimeHours,TimeMin,0)as time, Condcode,
      DialedNum, q.type ,q.name,q.description, CallingNum,p.name,p.description,
      MAKETIME(DurHours,DurMin,(Dur10ofMin*0.6)*10) as Duration
      FROM col_data_test c,  qos_digit_all q, qos_digit_all p
      WHERE DialedNum LIKE CONCAT(q.number,'%')
      AND  q.number = (SELECT MAX(number)
                        FROM qos_digit_all
                        WHERE c.DialedNum
                        LIKE CONCAT(number, '%'))
      AND CallingNum LIKE CONCAT(p.number,'%')
      AND  p.number = (SELECT MAX(number)
                        FROM qos_digit_all
                        WHERE p.CallingNum
                        LIKE CONCAT(number, '%'))
order by id

Jeg ved ikke hvad jeg skal gøre for at optimere forspørgelsen. Er der noget jeg kan gøre ? Kan jeg bruge Materialiseret views (lidt ligesom oracle). Jeg havde oprindeligt tiltænkt mig at køre forspørgelsen hver 2-3 sekund. Men det ser ikke ud til jeg kan klare det. vil en where betingelse kunne gøre denne joint meget hurtigere? (hvis jeg sortere på timestamp mindre end 5 minuter vil jeg nok ikke få mere end 50 poster fra col_data_test. er dette en gangbar løsning eller bør jeg klare problematikken i mit java program/php kode ??

Jeg vil tanke for din vedholdenhed og accepter dit svar ud fra denne del af sql-sætningen, håber den vil komme andre til gode
-------------------------------------------------------
WHERE DialedNum LIKE CONCAT(q.number,'%')
AND  q.number = (SELECT MAX(number)
                      FROM FROM qos_digit_all
                      WHERE c.DialedNum LIKE CONCAT(number, '%'))
--------------------------------------------------------
hvor jeg kan forstå at det aggregatfunktionen max() det gør tricket. det spøjse i den sammenhæng er at 'number' egentlig er en varchar, men det virker på trods.

Ja, det er MAX(), der gør det, men det er nu egentlig ikke så mærkeligt. Den funktion returnerer bare den største værdi (og værdi kan i den sammenhæng godt være en streng). Så når den skal sortere en række værdier som

"00034"
"000345"
"0003"

i nedadgående rækkefølge (DESC) hvor sættet er afgrænset af, at alle cifre jo skal være indeholdt i det nummer der søges på, vil resultatet blive

"000345"
"00034"
"0003"

Den længste streng vil altså altid også være den "højeste", da alle de øvrige tegn jo er de samme - hvilket sikres af den første LIKE sætning, hvor der sammenlignes med telefonnummeret.


Med hensyn til optimeringen, har du så prøvet min alternative "LIKE":

SELECT id,q.number, Time as Timestamp,MAKETIME(TimeHours,TimeMin,0)as time, Condcode,
      DialedNum, q.type ,q.name,q.description,
      MAKETIME(DurHours,DurMin,(Dur10ofMin*0.6)*10) as Duration
FROM col_data_test c,  qos_digit_all q
WHERE q.number = SUBSTRING(DialedNum, 1, LENGTH(q.number))
AND  q.number = (SELECT MAX(number)
                      FROM FROM qos_digit_all
                      WHERE number = SUBSTRING(c.DialedNum, 1, LENGTH(number)))
order by id

Måske virker dén jo også (og er måske endda endnu hurtigere, for LIKE har ikke et godt ry for hastighed).

--- (Den viste form med SUBSTRING er naturligvis ikke en fuldgod erstatning for LIKE, da den kun kan erstatte formen, hvor starten er fastlagt, altså LIKE 'xxxx%', mens den ikke kan bruges ved mere komplekse former som LIKE '%xxxx%'.

Desuden ved jeg ikke rigtigt hvordan MS SQL vil reagere i en situation, hvor DialedNum eller CallingNum er kortere end number, og SUBSTRING altså "skyder over målet" med længdeangivelsen. Jeg ved ikke, om den situation kan opstå, men du bør nok teste det...

Hvordan ser det ud med indekser på dine tabeller. Det kan gøre en stor forskel, hvis du har et index på de rigtige felter. I dette tilfælde nok især DialdNum, CallingNum og Prefix (number).

Hvis det er en mulighed, vil en afgrænsning til telefonopkald foretaget inden for de sidste 5 minutter da helt klart gøre forespørgslen hurtigere. Især hvis du har et index på dit timestamp felt.

Et materialized view, eller Indexed view, som det vist hedder hos SQL server, er da så absolut en mulighed som næsten sikkert vil forøge hastigheden mange gange.

optimering med begrænsning af kun 200 felter virker forrygende ca. 35ms.

p.s. hvis DialedNum, CallingNum i like klausulen, returneres den ikke. så der må en outjoin til jeg har ikke forsøgt mig med denne endnu. Det tager mig lidt lang tid der her MySQL og SQL det er ikke noget jeg har arbejdet med rigtigt. Tak for svaret om ikke andet. Jeg overvejer dog stadig og lade java/php koden klare ærterne.

"optimering med begrænsning af kun 200 felter virker forrygende ca. 35ms."
Tillykke med det! (Jeg går ud fra, at du mener 200 rækker?) :-)

"p.s. hvis DialedNum, CallingNum i like klausulen, returneres den ikke."
Den sætning forstår jeg ikke. Det er vist blevet forkortet lidt for meget...

Jeg har tænkt på en ting mere, som måske ville gøre din forespørgsel hurtigere. Umiddelbart synes jeg ikke dit view qos_digit_all er helt optimal. Det er måske en lille ting, men at placere en subselect i selectdelen synes ikke optimalt. Så jeg ville ændre den til:

select
_utf8'local' AS `type`,
`qos_users`.`extn` AS `number`,
`qos_users`.`name` AS `name`,
`qos_users`.`fullname` AS `description`

from `qos_users`
union

select
_utf8'global' AS `global`,
concat(`qos_config`.`dialout`,`qos_digit_view`.`prefix`) AS `prefix`,
`qos_digit_view`.`provider` AS `provider`,
`qos_digit_view`.`service` AS `service`

from `qos_config`,`qos_digit_view`

Indeholder CallingNum forøvrigt også Dialout prefixet?

Hvis det ikke gør det, vil det andet det aldrig komme til at virke, hvis du slår op i dit view qos_digit_all, som jo netop tilføjer dette til det oprindelige prefix fra tabellen qos_digit_list. I så fald er det ikke så sært, at du ikke får nogen rækker retur ved kørslen af din forespørgsel i 03/10-2006 23:52:53.

Ja det gør den
Når jeg modtager data fra pbx'en sætter den altid 0 foran indkomende kald, hvilket vil sige at hvis jeg ringer ud med "0" vil indgående nr også være prefix'et med "0".
Jeg ændre ikke på denne værdi, men når jeg vil sammenligne den i med min nummerplan (qos_digit_all), vil jeg blive nød til at indsætte 0 foran Nationale/eksterne numre.

Dette gør jeg via en select sætning concat(SELECT dialout from settingstabel,prefix))
denne dialout from settingstabel skulle gerne hente værdien '0'

dette bliver så til view som så køres union med den interne lokal nummerplan, dette view heder så qos_digit_all.

Er mit svar helt hen i skoven ??
Min forspørgelse virker, men hvis DialedNum eller CallingNum er "null" forsvinder posten, jeg går ud fra dette hænger sammen med at denne "null" værdi ikke er tilgængelig i qos_digit_all. Jeg ved ikke lige hvordan jeg løser dette for prefix er nøglen (ja jeg kunne jo oprette en ny)

Det sidste burde du hurtigt kunne ordne med en IFNULL funktion:

IFNULL(DialedNum, '0')

som vil returnere DialedNum, undtagen hvis dette felt er Null. Så vil den returnere en streng '0'. Hvis du alle steder, hvor du bruger DialedNum og CallingNum i din WHERE i stedet bruger den funktion, burde du i det mindste kunne få disse rækker med, forudsat at "erstatningsnummeret" findes i prefix tabellen.

"Dette gør jeg via en select sætning concat(SELECT dialout from settingstabel,prefix))
denne dialout from settingstabel skulle gerne hente værdien '0'

dette bliver så til view som så køres union med den interne lokal nummerplan, dette view heder så qos_digit_all."
----

Det var også sådan jeg forstod det. Det jeg ville sige med min kommentar 23.59.00 var bare, at selvom det er helt legitimt og virker, tror jeg ikke det er helt optimalt at placere en subquery på det sted. For hver række der læses i dit view qos_digit_view, skal den åbne, læse og lukke tabellen qos_config for at indlæse den ene række i tabellen som indeholder feltet dialout.
Jeg mener det er bedre at placere tabellerne sideordnet, således at den ene række i qos_config læses én gang (og bruges som "svingdør") i en CROSS JOIN (som producerer et "Cartesian Product"), hvorefter alle rækkerne i qos_digit_view gennemlæses. Det er en mere effektiv proces. Se evt. artiklen her: http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx