select help - multiple where/limits

[Kris Burford]

hi

wondering whether someone can set me straight on whether it's possible to 
request a set of records from a single table with multiple conditions.

for instance, a story table, containing id, title, text, section and 
published_date. what i would like is to retrieve is the 5 most recently 
published stories from each section (currently there are nine sections).

so, do i have to do this in nine separate queries or can i do something like:

SELECT id, title, text, sectioned, published_date
FROM stories
WHERE (section = 'events'  order by published_date desc limit 5) and 
(section = 'features'  order by published_date desc limit 5)

etc...

many thanks

kris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query Optimization

[Kris Burford]

Hi,

I'm trying to sort out a query that identifies images that are not in the 
story table (structures below). Peter Brawley has kindly pointed me in the 
right direction with the sql structure (which does work), but I'm getting 
benchmarks of 5+ seconds on the test data, whereas the live site has 50x 
the number of rows and I'm concerned about trying to go live with it.

If anyone could suggest ways to improve the query/table structure, I'd be 
most grateful.

Kris

SELECT i.image_id, i.image_name
FROM table_image i
LEFT JOIN table_story s
USING (mainpic_id)
WHERE s.mainpic_id IS NULL
ORDER by mi.image_name
TABLE IMAGE
image_id int(11) unsigned NOT NULL auto_increment,
  image_name char(64) NOT NULL default 'default',
  PRIMARY KEY  (mainpic_id),
  KEY mainpic_id (mainpic_id)
) TYPE=MyISAM
TABLE STORY
  id int(11) NOT NULL auto_increment,
  body text,
  image_id int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY id (id)
) TYPE=MyISAM
And the results from EXPLAIN SELECT:
table = image
type = ALL
possible keys =
key =
key_len =
ref =
rows = 460
extra =
table = story
type = ALL
possible keys =
key =
key_len =
ref =
rows = 610
extra =
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Optimization [sorted]

[kris burford]

I'm trying to sort out a query that identifies images that are not in the 
story table (structures below). Peter Brawley has kindly pointed me in the 
right direction with the sql structure (which does work), but I'm getting 
benchmarks of 5+ seconds on the test data, whereas the live site has 50x 
the number of rows and I'm concerned about trying to go live with it.
Went back to the manual and realised that the table wasn't properly 
indexed. Makes a *big* difference!

Sorry to waste your time/bandwidth.

Kris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

query reference help

[Kris Burford]

hi

i'm having problems trying to write a query that returns a list of images 
and the stories (if any) that they're associated with. in this case, the 
story references the image embedded into the story body text in the format 
[img]myimage.jpg[/img]

SELECT i.id, i.image_name, i.image_width, i.image_height, s.id as story_id, 
s.title
FROM images i
LEFT JOIN stories s
ON s.body like '[img]' + i.image_name + '[/img]'
WHERE i.image_name like '%$search%'
ORDER by i.image_name

this returns the appropriate images, but doesn't pick up any associated 
stories (which i *know* exist).

i'm guessing that it's something to do with the ON clause, but i don't 
understand what's wrong.

many thanks

kris
--
kris burford
midtempo ltd
http://www.midtempo.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query reference help

[kris burford]

  SELECT i.id, i.image_name, i.image_width, i.image_height, s.id as story_id,
  s.title
  FROM images i
  LEFT JOIN stories s
  ON s.body like '[img]' + i.image_name + '[/img]'
  WHERE i.image_name like '%$search%'
  ORDER by i.image_name
peter wrote:

Is

   ...LIKE '%[img]' + i.image_name + '[/img]%'

what you're looking for?
unfortunately not. i'd tried this without success and even set up some 
dummy text in a story with *just* the [img]myimage.jpg[/img] text.

still doesn't find it...

kris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]