Optimering af stor DB

Prøv at give os hele strukturen af din tabel samt nogle eksempler af dataene.

CREATE TABLE IF NOT EXISTS `ids` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `TUR_Group` varchar(15) NOT NULL,
  `TUR_C_ID` varchar(30) NOT NULL,
  `TUR_C_Name` varchar(40) NOT NULL,
  `Process_Type` varchar(15) NOT NULL,
  `Process_Start_Date` datetime NOT NULL,
  `Process_End_Date` datetime NOT NULL,
  `Hostname` varchar(50) NOT NULL,
  `Hostname_trim` varchar(40) NOT NULL,
  `OS` varchar(20) NOT NULL,
  `User_ID` varchar(20) NOT NULL,
  `Description` varchar(50) NOT NULL,
  `Status` varchar(15) NOT NULL,
  `Lastlogon` varchar(15) NOT NULL,
  `Groups` longtext NOT NULL,
  `Privileges` longtext NOT NULL,
  `MECID` varchar(15) NOT NULL,
  `without_PCHI` varchar(100) NOT NULL,
  `TUR_data_week` int(2) NOT NULL,
  `TUR_data_year` int(4) NOT NULL,
  `TUR_data_inserted` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Process_Start_Date` (`Process_Start_Date`,`Hostname`,`User_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13326028 ;

Og her er et eksempel på en datarække, som illustrerer hvordan alle rækker ser ud
2 | TUR | dk-tur-go | #DK_10#test |A|2010-11-09 15:59:45 | 2009-12-01 04:04:04 | GOGO-TSI-02 | GOGO-TSI-02 | MANUAL | TestID | This is a description | disabled | 00/00/0000 | | Non | 5 | 2010 | 2010-11-16 13:01:10

1. Du har ikke 3 unikke nøgler. Du har én sammensat unik nøgle. Dette betyder at du kan have flere forskellige rækker med samme `Process_Start_Date`, flere forskellige rækker med samme `Hostname` osv., men kun én række må have en given kombination af de tre felter!

2. Hvad søger du på? Dette er altafgørende for hvordan tabellen kan optimeres.
Prøv at skrive "EXPLAIN" foran din SELECT-forspørglse og se på hvad den skriver (vi skal nok hjælpe dig :).

1. Det er korrekt jeg har en sammensat nøgle. Det er sådan det skal være. Det undrede mig bare at den ene stod uden for parantes som om den var "vigtigere" end de andre.

2.Eksempel på søgning
EXPLAIN SELECT * FROM IDs WHERE User_ID LIKE '%$search%' OR Description LIKE '%$search%'
id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     IDs     ALL     NULL     NULL     NULL     NULL     13326027     Using where
(kopier det til en norepad, så står det pænere)


Og et andet eksempel
EXPLAIN SELECT Description, id, Hostname, User_ID, MIN( URT_data_week ) AS minuge FROM IDs GROUP BY User_ID, Hostname
HAVING minuge = '46'

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     IDs     ALL     NULL     NULL     NULL     NULL     13326027     Using temporary; Using filesort


Kan man evt optimere noget med at bruge flere tråde? Eller skulle jeg benytte en USB-nøgle, altså som flash drev til at optimere noget?

UNIQUE KEY `Process_Start_Date` (`Process_Start_Date`,`Hostname`,`User_ID`)

er et index ved navn process_start_date som bestaar af 3 felter inkl. et felt med samme navn.

Hej Arne :)
Det andet eksempel her, var jo den jeg fik af dig. Jeg har ikke haft til at gennemtjekke at resultatet er korrekt, men det tror jeg det er.
Men den tager laaang tid. Og ja, databasen er på 13 mio rækker pt. Kan du ud fra hvordan min struktur af tabellen sige mig om det kan gøres anderledes for at gøre det hurtigere?

Jeg har forsøgt at læse mig lidt til hvad "AS" og "HAVING" gør men forstår ikke sammenhængen. Kan du kort forklare mig hvad den query egentlig gør? Også MIN( tjektjek ), hvad gør den?

Okay, vi skal have lidt mere at vide om hvad din tabel skal indeholde, for det ser ud som om du måske har brug for lidt normalisering.
Men først, dit andet eksempel og AS og HAVING:
AS bruges til at lave et alias. I dit tilfælde beregner du en kolonne ("MIN( URT_data_week )"), og stoppede du bare dér, behøvede du ikke "AS minuge". Kørte du SQL-sætningen i phpmyadmin eller en anden MySQL browser (kan anbefale HeidiSQL) vil kolonnen hedde "MIN( URT_data_week)". Men det er besværligt at henvise til i andre dele af koden, så du kalder den blot for "minuge". Tilsvarende kan du kalde tabeller for andre ting, for at gøre koden lettere at læse, eksempelvis:
[code]SELECT u.id, u.name, m.subject FROM users as u
INNER JOIN messages as m ON m.user_id = u.id;[/code]
HAVING er lidt ligesom WHERE. Selv ville jeg have startet med at prøve at skrive "WHERE minuge = 46" og opdaget det ikke virkede. Filtrering på aggrationskolonner (kolonner med MIN, MAX, COUNT, SUM, etc. i forbindelse med GROUP BY) skal gøres med en HAVING.


Tilbage til dine data.
Hvad er det de indeholder? Er det en log? Du har nogle grupper; TUR, host, process, brugeren osv.
Er brugeren for samtlige 13 mio rækker forskellige, eller har du kun en håndfuld brugere?
Dine datatyper kan også optimeres: "TUR_data_week" behøver vel kun tal i størrelsen 0-53 mens "TUR_data_year" er vel et årstal. Du kan med fordel ændre dem til:
[code] `TUR_data_week` TINYINT UNSIGNED NOT NULL,
`TUR_data_year` YEAR NOT NULL,[/code]
Tallet i parentes bag et heltals deklaration angiver blot hvor mange cifre der skal vises. Se mere om datatyper i MySQL her: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
Og når du nu søger på "TUR_data_week" kan den med fordel også indekseres. Når du så søger, vil MySQL blot slå op i indekset. Det vil du også få at vide med EXPLAIN, men den vender vi tilbage til senere.

Hvad er det præcist du forsøger, i begge dine eksempler?

SELECT * FROM IDs WHERE User_ID LIKE '%$search%' OR Description LIKE '%$search%'

er en dræber performance mæssigt. LIKE 'xxx%' kan bruge et index men hverken LIKE '%xxx' eller LIKE '%xxx%' kan bruge et index. Med store data mængder går det bare langsomt.

I en del tilfælde kan man forbedre performance ved at bruge FULL TEXT søgning.

SELECT Description, id, Hostname, User_ID, MIN( URT_data_week ) AS minuge FROM IDs GROUP BY User_ID, Hostname
HAVING minuge = '46'

kunne nok bruge index på:
  URT_data_week
  User_ID
  Hostname

En kort forklaring hvad det bruges til.
Vi har en række servere, Hostname. På dem kører vi en udtræk en gang om måneden til at se hvilke brugere vi har derpå, User_IDs. Tidspunkten hvorpå udtrækket foretages er Process_Start_Date.

Jeg får disse udtræk i en samlet datapakke som indholder udtræk fra de sidste 3 måneder. Dem indlæser jeg i databasen. Og fordi jeg har en kombineret nøgle af process_start_date, Hostname og userID sikre jeg at jeg ikke får dobbelt data.

Når jeg har disse data er der 2-3 søgninger jeg skal bruge disse data til.

1. Finde alle NYE UserIDs som er indlæst i uge xx. Jeg indlæser kun data én gang om ugen; TUR_data_week. Så jeg skal på en måde finde de UserIDs på hver Hostname som ikke tidligere har været på den samme Hostname.
Det er denne at du Arne foreslår denne
SELECT Description, id, Hostname, User_ID, MIN( URT_data_week ) AS minuge FROM IDs GROUP BY User_ID, Hostname
HAVING minuge = '46'

2. Jeg skal søge på et bestemt userID og få returneret de servere, Hostname, hvorpå IDet ligger. Og da man ikke kan vide med sikkerhed hvor UserID er stavet, er jeg nødt til at bruge LIKE %%. Og måden at gøre det på er at se på det sidste udtræk for hver Hostname.
Min primitive måde er at først lave denne
SELECT * FROM IDs WHERE User_ID LIKE '%$search%' OR Description LIKE '%$search%', her finder jeg bare ALLE rækker hvor jeg kan finde IDet.
For hver række, finder jeg så datoen, Process_start_date, for det seneste udtræk. Hvis den nuværende række ikke er af den fundne process_start_date, så er rækken forældet og så springer jeg den over.
Det virker efter hensigten, men er langsom :(

For mig synes din datamodel at være lidt rodet, så jeg kan ikke hjælpe dig videre.
Men der hvor du bestemt kan vinde en del, er at indeksere de felter du søger på (User_ID, Hostname etc.) som almindelige, enkelte indekser.
Noget der slog mig, er at du ikke kan vide med sikkerhed hvordan UserID er stavet. For hvis de ikke er unikke har du et kæmpe dataintegritetsproblem. Kort sagt, hvordan kan du se forskel på "MrH" og "MrHey"? Hvis du leder efter "MrH" får du begge to.

Det jeg læser er, at I vil vide hvilke brugere er benyttet på hvilke hosts, og have en lang optegnelse af dette der går lang tid tilbage. Dette har du i én tabel med UserID, Hostname og Proces_date (== registreret første gang). Denne er indekseret med et unikt, sammensat indeks på først Hostname, så UserID. Proces_date er blot en timestamp. Og så skal du i gang med noget scripting - enten Python, php eller bare MySQL:
1. Indeksere Hostname og UserID i din datapakke. (Tager tid!)
2. Kør følgende (sådan ca):
[code]INSERT IGNORE INTO MyUserRecords (UserID, Hostname) FROM SELECT DISTINCT UserID, Hostname FROM MyDataPackage;[/code]

Dette sikrer at kun de nye userID x Hostname kombinationer indsættes. Datoen for hvornår de er indsat er tidspunktet for hvornår koden ovenfor er kørt. Hvis du vil have proces date kan det også gøres, men kræver et par ekstra mysql-kommandoer.

Hvordan benytter man en FULL TEXT søgning i forbindelse med LIKE %%?

Til at finde alle Hostnames hvorpå der ved senest udtræk er fundet et bestemt userID, kan man lave noget i retning af

select userID, Hostname, process_start_data AS mitID where User_ID LIKE '%$search%' and process_start_data=(select process_start_date from IDs where Hostname='mitID.Hostname' order by process_start_date DESC limit 0,1).

Hvis man kan det, måske skriver jeg ikke korrekt syntax, så er det faktisk det jeg gerne vil have

start her:

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

Jeg har lige prøvet at lave min User_ID til Full TEXT og kan godt se der er gode muligheder for at gøre søgningerne meget hurtigere.

Lige nu leger jeg med denne query
SELECT *, MATCH(User_ID) AGAINST('G99999') FROM IDs WHERE MATCH(User_ID) AGAINST('G99999')

Den finder alle hvor User_ID er "G99999", men den finder intet hvis jeg istedet søger på bare "99999". Den skulle jo gerne erstatte LIKE %99999%.

FULL TEXT er designet til hele ord. mySQL FULLTEXT understøtter suffix wildcards men ikke prefix wildcards.

Hvis det er kravet skal du nok ud i en custom løsning.

Jeg tror ikke vi når meget længere nu. Men jeg er kommet et godt stykke og har fået noget af det til at virke rigtig godt med jeres hjælp.

Arne, læg gerne point for din hjælp.

svar