MySQL Søgninger

Kvikke hoveder, finder sikkert hurtigt ud af, at det er fagguiden.dk der er tale om...

Hele idéen med projektet er, at konvertere fra ASP til PHP og MySQL.

Vi skal lige se dine tabeldefinitioner, det vil gøre det noget nemmere

Hvorfor bruger du $firma_cat i denne where-sætning

WHERE categorycompanylinks.CategoryKey = \'$firma_cat\'\";

når det er $key du henter fra $row?

Vis forresten dine tabeldefinitioner som CREATE TABLE sætninger, så kan vi også se, om du har defineret ordentlige indekser

Vores tabelstruktur:
# Struktur dump for tabellen `categories`
#

CREATE TABLE categories (
  CategoryKey int(30) NOT NULL default \'0\',
  Name varchar(80) NOT NULL default \'\',
  NameUK varchar(80) NOT NULL default \'\',
  PRIMARY KEY  (CategoryKey)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `categorycompanylinks`
#

CREATE TABLE categorycompanylinks (
  CategoryCompanyLinkKey int(11) NOT NULL auto_increment,
  CategoryKey int(11) default NULL,
  CompanyKey int(11) default NULL,
  LinkExpire datetime default NULL,
  PRIMARY KEY  (CategoryCompanyLinkKey)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `companies`
#

CREATE TABLE companies (
  CompanyKey int(25) NOT NULL default \'0\',
  RegionKey int(25) NOT NULL default \'0\',
  Name varchar(160) NOT NULL default \'\',
  Occupation varchar(80) NOT NULL default \'\',
  Address varchar(80) NOT NULL default \'Ej Oplyst\',
  Postcode int(25) NOT NULL default \'0\',
  CounsilKey int(25) NOT NULL default \'0\',
  Phone varchar(50) NOT NULL default \'\',
  Fax varchar(50) NOT NULL default \'\',
  Mobile varchar(50) NOT NULL default \'\',
  Owner varchar(50) NOT NULL default \'\',
  Email varchar(80) NOT NULL default \'\',
  WWW varchar(80) NOT NULL default \'\',
  Shop varchar(80) NOT NULL default \'\',
  KeyWords varchar(255) NOT NULL default \'\',
  KeyProducts varchar(255) NOT NULL default \'\',
  Graphics varchar(255) NOT NULL default \'\',
  GraphicsOnly varchar(255) NOT NULL default \'\',
  GraphicsExpire varchar(255) NOT NULL default \'\',
  EmailExpire varchar(255) NOT NULL default \'\',
  WWExpire varchar(255) NOT NULL default \'\',
  ShopExpire varchar(255) NOT NULL default \'\',
  KeywordsExpire varchar(255) NOT NULL default \'\',
  KeyProductsExpire varchar(255) NOT NULL default \'\',
  ContactPerson varchar(80) NOT NULL default \'\',
  ContactPhone tinyint(50) NOT NULL default \'0\',
  CompanyDesc varchar(255) NOT NULL default \'\',
  ModifiedByUser varchar(255) NOT NULL default \'\',
  ModifiedByUserDate varchar(255) NOT NULL default \'\',
  Modified varchar(255) NOT NULL default \'\',
  Newlink varchar(255) NOT NULL default \'\',
  CategoryKey int(25) NOT NULL default \'0\',
  PRIMARY KEY  (CompanyKey)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `html`
#

CREATE TABLE html (
  CompanyKey int(25) NOT NULL default \'0\',
  Info text NOT NULL,
  id int(25) NOT NULL auto_increment,
  PRIMARY KEY  (id)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `nyhedsbrev`
#

CREATE TABLE nyhedsbrev (
  email varchar(100) NOT NULL default \'\',
  notes varchar(255) NOT NULL default \'\',
  dato varchar(255) NOT NULL default \'\',
  id int(20) NOT NULL auto_increment,
  PRIMARY KEY  (id),
  UNIQUE KEY email (email,id)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `phpads_acls`
#

CREATE TABLE phpads_acls (
  bannerID mediumint(9) NOT NULL default \'0\',
  acl_con set(\'and\',\'or\') NOT NULL default \'\',
  acl_type enum(\'clientip\',\'useragent\',\'weekday\',\'domain\',\'source\',\'time\',\'language\') NOT NULL default \'clientip\',
  acl_data varchar(255) NOT NULL default \'\',
  acl_ad set(\'allow\',\'deny\') NOT NULL default \'\',
  acl_order int(10) unsigned NOT NULL default \'0\',
  PRIMARY KEY  (bannerID,acl_order),
  KEY bannerID (bannerID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `phpads_adclicks`
#

CREATE TABLE phpads_adclicks (
  bannerID mediumint(9) NOT NULL default \'0\',
  t_stamp timestamp(14) NOT NULL,
  host varchar(255) NOT NULL default \'\',
  KEY clientID (bannerID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `phpads_adstats`
#

CREATE TABLE phpads_adstats (
  views int(11) NOT NULL default \'0\',
  clicks int(11) NOT NULL default \'0\',
  day date NOT NULL default \'0000-00-00\',
  BannerID smallint(6) NOT NULL default \'0\',
  PRIMARY KEY  (day,BannerID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `phpads_adviews`
#

CREATE TABLE phpads_adviews (
  bannerID mediumint(9) NOT NULL default \'0\',
  t_stamp timestamp(14) NOT NULL,
  host varchar(255) NOT NULL default \'\',
  KEY clientID (bannerID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `phpads_banners`
#

CREATE TABLE phpads_banners (
  bannerID mediumint(9) NOT NULL auto_increment,
  clientID mediumint(9) NOT NULL default \'0\',
  active enum(\'true\',\'false\') NOT NULL default \'true\',
  weight tinyint(4) NOT NULL default \'1\',
  seq tinyint(4) NOT NULL default \'0\',
  banner blob NOT NULL,
  width smallint(6) NOT NULL default \'0\',
  height smallint(6) NOT NULL default \'0\',
  format enum(\'gif\',\'jpeg\',\'png\',\'html\',\'url\',\'web\',\'swf\') NOT NULL default \'gif\',
  url varchar(255) NOT NULL default \'\',
  alt varchar(255) NOT NULL default \'\',
  status varchar(255) NOT NULL default \'\',
  keyword varchar(255) NOT NULL default \'\',
  bannertext varchar(255) NOT NULL default \'\',
  target varchar(8) NOT NULL default \'\',
  description varchar(255) NOT NULL default \'\',
  autohtml enum(\'true\',\'false\') NOT NULL default \'true\',
  PRIMARY KEY  (bannerID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `phpads_clients`
#

CREATE TABLE phpads_clients (
  clientID mediumint(9) NOT NULL auto_increment,
  clientname varchar(255) NOT NULL default \'\',
  contact varchar(255) default NULL,
  email varchar(64) NOT NULL default \'\',
  views mediumint(9) default NULL,
  clicks mediumint(9) default NULL,
  clientusername varchar(64) NOT NULL default \'\',
  clientpassword varchar(64) NOT NULL default \'\',
  expire date default \'0000-00-00\',
  activate date default \'0000-00-00\',
  permissions mediumint(9) default NULL,
  language varchar(64) default NULL,
  active enum(\'true\',\'false\') NOT NULL default \'true\',
  weight tinyint(4) NOT NULL default \'1\',
  parent mediumint(9) NOT NULL default \'0\',
  report enum(\'true\',\'false\') NOT NULL default \'true\',
  reportinterval mediumint(9) NOT NULL default \'7\',
  reportlastdate date NOT NULL default \'0000-00-00\',
  reportdeactivate enum(\'true\',\'false\') NOT NULL default \'true\',
  PRIMARY KEY  (clientID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `phpads_session`
#

CREATE TABLE phpads_session (
  SessionID varchar(32) NOT NULL default \'\',
  SessionData blob NOT NULL,
  LastUsed timestamp(14) NOT NULL,
  PRIMARY KEY  (SessionID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `phpads_zones`
#

CREATE TABLE phpads_zones (
  zoneid mediumint(9) NOT NULL auto_increment,
  zonename varchar(255) NOT NULL default \'\',
  description varchar(255) NOT NULL default \'\',
  zonetype smallint(6) NOT NULL default \'0\',
  what blob NOT NULL,
  width smallint(6) NOT NULL default \'0\',
  height smallint(6) NOT NULL default \'0\',
  retrieval enum(\'random\',\'cookie\') NOT NULL default \'random\',
  cachecontents blob,
  cachetimestamp int(11) NOT NULL default \'0\',
  PRIMARY KEY  (zoneid)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `postcodes`
#

CREATE TABLE postcodes (
  Postcode int(4) NOT NULL default \'0\',
  City varchar(50) NOT NULL default \'\',
  RegionKey int(30) NOT NULL default \'0\'
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `regions`
#

CREATE TABLE regions (
  RegionName varchar(30) NOT NULL default \'\',
  RegionKey int(2) NOT NULL default \'0\',
  PRIMARY KEY  (RegionKey),
  UNIQUE KEY RegionName (RegionName,RegionKey)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Struktur dump for tabellen `towns`
#

CREATE TABLE towns (
  Townkey int(30) NOT NULL default \'0\',
  CityName varchar(255) NOT NULL default \'\',
  CounsilKey int(30) NOT NULL default \'0\',
  CountyKey int(30) NOT NULL default \'0\',
  Postcode int(4) NOT NULL default \'0\',
  PRIMARY KEY  (Townkey)
) TYPE=MyISAM;

> Hvorfor bruger du $firma_cat i denne
> where- sætning
>
> WHERE categorycompanylinks.CategoryKey
> = \'$firma_cat\'\";
>
> når det er $key du henter fra $row?

Alle firmaer har et id. Dette id lægger også i categorycompanylinks, ved siden af et nummer til en bestemt kategori. Eftersom nogle firmaer lægger i flere forskellige kategorier, skal den hente alle firmaer hvor deres id optræder ved siden af det pågældende kategori nummer.

$firma[$i] indeholder nummeret på den kategori folk ønsker at se, her kan de fx. have krydset flere af på en gang.

WHERE categorycompanylinks.CategoryKey = \'$firma_cat\'\";

skal faktisk rettes til :

WHERE categorycompanylinks.CategoryKey = \'$firma[$i]\'\";

Et par spørgsmål:

Hvad er $firma?

Prøv at forklare (uddybende) hvad det er, der skal søges på i ovenstående eksempel.

Prøv lige at analysere de queries i kører. Skriv EXPLAIN foran de SELECT i har og se hvordan MySQL benytter de index der er. Skriv resultatet her så vi kan se om alt er som det skal være.

Er der nok ram i serveren så den kan have hele tabellen i ram? Hvis ikke tager det sku lang tid når mysql lave en temp fil på disk.

Jeg har ikke selv oprettet indeks, så hvis ikke selv mysql gør det, så er der ingen index...

$sql = \"Explain SELECT
companies.Name,
companies.Address,
companies.Postcode,
companies.Phone,
companies.Fax,
companies.WWW,
companies.Shop,
companies.CompanyKey,
companies.Email,
companies.CompanyDesc,
postcodes.PostCode,
postcodes.City,
html.Info
FROM categorycompanylinks
LEFT JOIN companies ON categorycompanylinks.CompanyKey = companies.CompanyKey
LEFT JOIN html ON html.CompanyKey = companies.CompanyKey
LEFT JOIN postcodes ON companies.PostCode = postcodes.Postcode
WHERE categorycompanylinks.CategoryKey = \'$firma[$i]\'\";

denne query giver ingenting...

fjerner jeg explain virker den...
$firma er nummeret på en given kategori.

Det er fordi du ikke skal gøre det i PHP, du skal gøre det i MySQL konsollen

alvion> skrive explain i konsollen eller oprette index i konsollen?

Den eneste konsol jeg har, er den WinMysqlAdmin der kører på win2k serveren...

Du skal skrive EXPLAIN sætningen i konsollen

WinMysqlAdmin burde kunne bruges, men har du ikke også en konsol, hvis du i en dos-prompt skriver:

c:\\mysql\\bin>mysql -ubrugernavn -pkodeord databasenavn

var det så ikke en idé jeg oprettede nogle index først?

Nej det skal du ikke.

EXPLAIN fortæller dig, hvorvidt din sql-forspørgsel benytter indekser eller ej. De steder, hvor der ikke bliver brugt indeks, kan du så undersøge de indekser du har, og finde ud af, hvorfor.

Det kan dog godt være, at du lige skal lave en primær nøgle på tabellen \"postcodes\" med postcode som nøgle.

jeg kan ikke helt følge dig, med din explain kommando. _Hvis_ mysql ikke opretter indeks selv, og jeg ikke har gjortet, skal jeg da oprette index selv, før skidtet kan benytte dem..

eller er jeg helt forkert på den?

kan jeg ikke få den præcise sti til en eller anden konsol..

skal jeg bruge dos konsollen og i så fald, hvad skal jeg skriver der? fx

explain select Name,Phone from companies where Name = \'%firmanavn%\'

det kan man sgu da ik skrive i dos...

Sorry:

explain select Name,Phone from companies where Name like \'%firmanavn%\'

1) Åben en dos-prompt. I den skriver du:
cd \\mysql\\bin
mysql -uditbrugernavn -pditpassword databasenavnet

2) Du har nu startet MySQL\'s konsol. Dette kan ses på prompten, hvor der står: \"mysql>\". Her skriver du så din EXPLAIN sætning.

Ved ikke, om du kan bruge det til noget!...

http://www.salgsmarked.dk/alvion.gif

morw> se den der fil, http://www.salgsmarked.dk/alvion.gif, der har du det index du spurgte efter... tror jeg ;-)

Ja problemet i den forespørgsel ligger i brugen af LIKE

Når du skriver \"WHERE Name LIKE \'%Danbrit%\' \" siger du til MySql \"Find alle poster, hvor ordet \'Danbrit\' indgår ET ELLER ANDET STED i feltet Name\". Så kan MySQL ikke bruge noget indeks, da et indeks på et tekstfelt laver en alfabetisk sortering af posterne fra begyndelsen af feltet.

Hvis du nu fjernede den første % og skrev \"WHERE Name LIKE \'Danbrit%\' \", betyder det \"Find alle poster, hvor feltet Name STARTER MED \'Danbrit\'\".

Dernæst opretter du et indeks på feltet Name således:

ALTER TABLE companies ADD INDEKS idx1(Name);

Så kører du den EXPLAIN igen (altså med den nye WHERE sætning) og ser, om ikke indekset \"idx1\" bliver brugt.

Hvis nu din kommentar er \"Jamen jeg SKAL kunne søge på Danbrit som en delstreng og ikke kun i starten af feltet Navn\", så er svaret FULLTEXT indeks.

Nu får du godt nok links til min beskrivelse af fulltext indeks, men prøv lige det andet af først.

http://www.eksperten.dk/spm/81387
http://www.eksperten.dk/spm/96630

Man kan ikke se din gif fil ?

Den har været der, men nu er den væk igen

Nu får jeg sikkert bank af dem alle, fordi jeg har givet henholdsvis 900 og 100 point væk ;-)

1000 tak for hjælpen, Carsten, du er satme dygtig...

Det var så lidt. :-) Hvilken løsning valgte du? Den med LIKE eller den med FULLTEXT indeks?

Og tak for roserne, jeg rødmer helt. :-)

jeg valgte full-text..

kan man også bruge et indeks til rækker med tal?

vil gerne søge hurtigere gennem telefon numre...

ved godt spørgsmålet er lukket, men...:-)

Der er flere svar til denne:

a) Ja du kan godt lave et indeks på tal, men ikke fulltext indeks. Det kan kun laves på VARCHAR, TEXT og LONGTEXT

b) Jeg ville nu nok ikke lave telefonnumre som talfelter, men som tekstfelter.

alvion> Telefonnummer i et tekst felt?

Det kan næppe være optimalt når man har 2 milioner af dem.

2 millioner? Nej så er det nok ikke :-)

Det er bare så tit, at folk har brug for at gemme andet end tal i et telefonnummer, derfor mit foreslag.

Ja, men fagguiden.dk er nok rimmeligt standarliseret hvad format angår. Ellers er det nok en god ting at starte med, hvis performance skal i top. Et index på et int felt er også hurtigere end char.

Helt enig - jeg havde glemt, hvad det skulle bruges til. Jeg har lige været en uge i sommerhus...