CS290F Fall 2006 - UCSB Computer Science - Thorsten von Eicken

Path 1 SQL Queries

SHOW FIELDS FROM users
-- Looks Ok

SELECT * FROM users WHERE (users.`login` = 'alevi' ) LIMIT 1
-- Looks Ok

SELECT * FROM users WHERE (users.`id` = 3 ) LIMIT 1
-- Looks Ok

SHOW FIELDS FROM joblistings
-- Looks Ok

SHOW FIELDS FROM employers
-- Looks Ok

SELECT joblistings.`id` AS t0_r0, joblistings.`employer_id` AS t0_r1, joblistings.`title` AS t0_r2, joblistings.`city` AS t0_r3,
joblistings.`state` AS t0_r4, joblistings.`dateposted` AS t0_r5, joblistings.`snippet` AS t0_r6, joblistings.`url` AS t0_r7, employers.`id` AS t1_r0, 
employers.`name` AS t1_r1 FROM joblistings LEFT OUTER JOIN employers ON employers.id = joblistings.employer_id  WHERE ((UCASE(city) LIKE '%CA%' OR 
UCASE(state) LIKE '%CA%' OR UCASE(employers.name) LIKE '%CA%' OR UCASE(title) LIKE '%CA%')) ORDER BY dateposted DESC LIMIT 0, 10
-- Needs optimization - We have replaced it with the following indexed query
SELECT * from joblistings where idxfti @@ to_tsquery('default', 'CA') ORDER BY dateposted DESC LIMIT 10 OFFSET 0

SELECT * FROM joblistings WHERE (joblistings.id = '129') LIMIT 1
-- Looks Ok

SELECT * FROM documents WHERE (user_id = 3) 
-- Looks Ok

SHOW FIELDS FROM documents
-- Looks Ok

SELECT * FROM employers WHERE (employers.id = 160) LIMIT 1
-- Looks Ok

SHOW FIELDS FROM apps
-- Looks Ok

SELECT * FROM apps WHERE (user_id = 3 AND joblisting_id = 105) 
-- Looks Ok

BEGIN
INSERT INTO apps (`status`, `joblisting_id`, `user_id`) VALUES('Applied', 105, 3)
COMMIT
-- Looks Ok

SHOW FIELDS FROM attachments
-- Looks Ok

BEGIN
INSERT INTO attachments (`document_id`, `app_id`) VALUES(6, 12)
COMMIT
-- Looks Ok