Imam sledecu tebelu(ovde su prikazan samo polja po kojima cu pretrazivati i sortirati a u tabeli ima jos nekoliko varchar polja ali mislim da to nije vazno u ovom slucaju)
Code:
CREATE TABLE car_listings(
pkListingID bigint(22) unsigned NOT NULL auto_increment,
country tinyint(3) unsigned NOT NULL default '0',
make int(11) unsigned NOT NULL default '0',
model int(11) unsigned NOT NULL default '0',
price int(11) unsigned NOT NULL default '0',
enterd int(11) unsigned default NULL,
PRIMARY KEY (pkListingID),
KEY ListingKey_2 (country,make,model,price)
) ENGINE=MyISAM;
CREATE TABLE car_listings(
pkListingID bigint(22) unsigned NOT NULL auto_increment,
country tinyint(3) unsigned NOT NULL default '0',
make int(11) unsigned NOT NULL default '0',
model int(11) unsigned NOT NULL default '0',
price int(11) unsigned NOT NULL default '0',
enterd int(11) unsigned default NULL,
PRIMARY KEY (pkListingID),
KEY ListingKey_2 (country,make,model,price)
) ENGINE=MyISAM;
i izvrsavacu sledece upite:
Code:
SELECT * FROM car_listings WHERE country=13 AND make=125 AND
model=562AND price>= 50000 AND price<=100000 ORDER BY enterd DESC;
SELECT * FROM car_listings WHERE make=125 AND model=562 AND
price>= 50000 AND price<=100000 ORDER BY enterd DESC;
SELECT * FROM car_listings WHERE make=125 AND price>= 50000 AND
price<=100000 ORDER BY enterd DESC;
SELECT * FROM car_listings WHERE country=13 AND make=125 AND
model=562AND price>= 50000 AND price<=100000 ORDER BY enterd DESC;
SELECT * FROM car_listings WHERE make=125 AND model=562 AND
price>= 50000 AND price<=100000 ORDER BY enterd DESC;
SELECT * FROM car_listings WHERE make=125 AND price>= 50000 AND
price<=100000 ORDER BY enterd DESC;
Da li neko ima iskustva sa razlikom u brzini izmendju gore kreirane tabele u poredjenju sa tabelom kod koje bi svaki index iz ListingKey_2 bio zaseban, znaci
Code:
CREATE TABLE car_listings(
pkListingID bigint(22) unsigned NOT NULL auto_increment,
country tinyint(3) unsigned NOT NULL default '0',
make int(11) unsigned NOT NULL default '0',
model int(11) unsigned NOT NULL default '0',
price int(11) unsigned NOT NULL default '0',
enterd int(11) unsigned default NULL,
PRIMARY KEY (pkListingID),
KEY country_key (country),
KEY make_key (make),
KEY model_key (model),
KEY price_key(price)
) ENGINE=MyISAM;
CREATE TABLE car_listings(
pkListingID bigint(22) unsigned NOT NULL auto_increment,
country tinyint(3) unsigned NOT NULL default '0',
make int(11) unsigned NOT NULL default '0',
model int(11) unsigned NOT NULL default '0',
price int(11) unsigned NOT NULL default '0',
enterd int(11) unsigned default NULL,
PRIMARY KEY (pkListingID),
KEY country_key (country),
KEY make_key (make),
KEY model_key (model),
KEY price_key(price)
) ENGINE=MyISAM;
Bilo kakav savet je dobro dosao. Tabela bi trebala da ima negde oko par stotina hiljada zapisa, verovatno ne vise od 500.000.