Here is the SHOW CREATE TABLE for the products table with the relevant columns:
CREATE TABLE `items` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`seller` varchar(30) NOT NULL,
`title` varchar(85) NOT NULL,
`listingtype` tinyint(1) NOT NULL,
`created` datetime NOT NULL,
`endtime` datetime NOT NULL,
`live` tinyint(1) NOT NULL,
`currentprice` decimal(10,2) NOT NULL,
`binprice` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `seller` (`seller`),
KEY `title` (`title`),
KEY `created` (`created`),
KEY `endtime` (`endtime`),
KEY `live` (`live`),
KEY `listingtype` (`listingtype`),
KEY `currentprice` (`currentprice`),
KEY `binprice` (`binprice`)
) ENGINE=InnoDB AUTO_INCREMENT=500000 DEFAULT CHARSET=utf8
There are more columns here that are relevant to our queries that you should know about but it could make it more complex for answering this question.
The “listingtype” column has a number (1 or 2) to tell us if the item is an “auction” or a “buy-it-now” item. We sometimes use the “listyingtype” column with the “binprice” column to see if an item is an auction with a “buy-it-now” option, if the binprice is > 0.00.
The “live” column tells us a number where “1” means the item is live and “2” means it is sold out and “3” means it is unsold.
The “created” column is the time an item is created, so we can sort newest listings first.
The “endtime” column is the time an item ends so we can sort which auctions/items are ending soonest.
The “currentprice” column is the current price of an item whether it is an auction or buy-it-now item, we use this to sort price (highest/lowest).
EXAMPLE QUERIES:
// Display ALL items, newest items
SELECT * FROM items USE INDEX (created) WHERE live = 1 ORDER BY created DESC LIMIT 0, 25
// Display ALL items, ending soonest
SELECT * FROM items USE INDEX (endtime) WHERE live = 1 ORDER BY endtime ASC LIMIT 0, 25
//Display ALL items, highest price first
SELECT * FROM items USE INDEX (currentprice) WHERE live = 1 ORDER BY currentprice DESC LIMIT 0, 25
//Display BIN (Buy It Now available) items, newest items
SELECT * FROM items USE INDEX (created) WHERE binprice != 0.00 AND live = 1 ORDER BY created DESC LIMIT 0, 25
//Display BIN items, ending soonest
SELECT * FROM items USE INDEX (endtime) WHERE binprice != 0.00 AND live = 1 ORDER BY endtime ASC LIMIT 0, 25
//Display BIN items, highest price first
SELECT * FROM items USE INDEX (currentprice) WHERE binprice != 0.00 AND live = 1 ORDER BY currentprice DESC LIMIT 0, 25
//Display AUCTION-ONLY items, newest items
SELECT * FROM items USE INDEX (created) WHERE listingtype = 1 AND live = 1 ORDER BY created DESC LIMIT 0, 25
//Display AUCTION-ONLY items, ending soonest
SELECT * FROM items USE INDEX (endtime) WHERE listingtype = 1 AND live = 1 ORDER BY endtime ASC LIMIT 0, 25
//Display AUCTION-ONLY items, highest price first
SELECT * FROM items USE INDEX (currentprice) WHERE listingtype = 1 AND live = 1 ORDER BY currentprice DESC LIMIT 0, 25
These are the more simple queries for page 1 of the homepage, each page having 25 items. On our member pages, we need to add the "WHERE seller = ‘joe’ " condition, or on our category pages, we add a category number and etc.
