Hello,
as i see you use one table to store all the data, but the cohesive data are
split into 15! different rows.
I think to get the best performance you shoud redesign your tabel.
Use at last first normal form [1NF], if the app_id is uniq this can be the
primary key [which will speed up the query] .
Data which have the same row_id should be in one row.
Your table definition shoud look like this:
create table content (
app_id MEDIUMINT NOT NULL AUTO_INCREMENT,
niche ??? , -- maybe int
type int,
title varchar(200),
description text,
image ???, -- maybe varchar
last_update datetime,
content_link varchar(200),
unique_id int,
date_added datetime,
content_provider int,
user_hits int,
vote_total int,
vote_user_total int,
channel int,
--...
primary key ( app_id )
);
As i see you get at last 129 different filed type [s9.field_id=129], you can
split the data into different tables. [Use heigher normal form.]
But if you don't want redesign the tables and all the different fields exists
then use join instead of left join and then the "group by" is needless.
burci
Thursday, October 23, 2003, 9:05:26 PM, you wrote:
MB> Hey All-
MB> I am trying to improve the speed of a website and was wondering if
MB> anyone had any ways I can rewrite these queries so that they actually
MB> run with some descent speed.
MB> Its a really nasty query and I'm not sure where to start, I'd like to
MB> now have to redo the tables and I already put some indexes on it which
MB> improved speed a lot but these queries are still very slow. You can most
MB> certainly laugh to yourselves on this one... jsut trying to get some
MB> opinions on what I should do with this.
MB> Thanks-
MB> Matt
MB> SELECT content.row_id AS row_id, content.app_id AS app_id, s1.data AS
MB> niche, s2.data AS type, s3.data AS title, s4.data AS description,
MB> s5.data AS image, s6.data AS last_update, s7.data AS content_link,
MB> s8.data AS unique_id, s9.data AS date_added, s10.data AS
MB> content_provider, s11.data AS user_hits, s12.data AS vote_total,
MB> s13.data AS vote_user_total, s14.data AS channel FROM content LEFT JOIN
MB> content s1 ON s1.field_id=69 AND s1.row_id = content.row_id LEFT JOIN
MB> content s2 ON s2.field_id=70 AND s2.row_id = content.row_id LEFT JOIN
MB> content s3 ON s3.field_id=71 AND s3.row_id = content.row_id LEFT JOIN
MB> content s4 ON s4.field_id=72 AND s4.row_id = content.row_id LEFT JOIN
MB> content s5 ON s5.field_id=73 AND s5.row_id = content.row_id LEFT JOIN
MB> content s6 ON s6.field_id=74 AND s6.row_id = content.row_id LEFT JOIN
MB> content s7 ON s7.field_id=76 AND s7.row_id = content.row_id LEFT JOIN
MB> content s8 ON s8.field_id=84 AND s8.row_id = content.row_id LEFT JOIN
MB> content s9 ON s9.field_id=129 AND s9.row_id = content.row_id LEFT JOIN
MB> content s10 ON s10.field_id=116 AND s10.row_id = content.row_id LEFT
MB> JOIN content s11 ON s11.field_id=118 AND s11.row_id = content.row_id
MB> LEFT JOIN content s12 ON s12.field_id=120 AND s12.row_id =
MB> content.row_id LEFT JOIN content s13 ON s13.field_id=121 AND s13.row_id
MB> = content.row_id LEFT JOIN content s14 ON s14.field_id=125 AND
MB> s14.row_id = content.row_id WHERE content.app_id = 11 AND
MB> unix_timestamp(s6.data)-unix_timestamp('2003-10-23 23:59:59') < 0 GROUP
MB> BY row_id ORDER BY last_update desc LIMIT -1
--
[nick]:burci [hp]:http://peter.buri.hu [mailto]:[EMAIL PROTECTED] [motto]:"Music makes
life easier to survive!"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
