Hey All-
I am trying to improve the speed of a website and was wondering if anyone had any ways I can rewrite these queries so that they actually run with some descent speed.
Its a really nasty query and I'm not sure where to start, I'd like to now have to redo the tables and I already put some indexes on it which improved speed a lot but these queries are still very slow. You can most certainly laugh to yourselves on this one... jsut trying to get some opinions on what I should do with this.
Thanks- Matt
Matt,
Instead of doing table joins on a large number of tables, in my application I've created a balance line procedure that would sync the "linked" tables with the primary table. (The tables are NOT linked using SQL) This works if there is a 1:1 correspondence between the tables. So each table has a simple Select statement for just that table and an optional Where clause and the table is sorted on a field that is common to all tables. If the key field of any of the secondary tables are less than the key field in the primary table, then the secondary records are skipped until it matches or exceeds the primary key field. It it exceeds the primary field then the secondary record is missing and you would use all null's for the secondary table.
Example: select cust_id, col1a, col1b from table1 order by cust_id select cust_id, col2a, col2b from table2 order by cust_id select cust_id, col3a, col3b from table3 order by cust_id
A GetNextRcd procedure would sync the records so it returns 3 records all pointing to the same cust_id. A loop would then traverse through the table.
The other solution would be to create a RAM disk for your MySQL work directory. This will speed up the table joins. MySQL allows for multiple work volumes so the first volume could be the ram disk. If it overflows that, I'm thinking it will go to your second work directory. I haven't tried it, but it may be something to look into.
Mike
SELECT content.row_id AS row_id, content.app_id AS app_id, s1.data AS niche, s2.data AS type, s3.data AS title, s4.data AS description, s5.data AS image, s6.data AS last_update, s7.data AS content_link, s8.data AS unique_id, s9.data AS date_added, s10.data AS content_provider, s11.data AS user_hits, s12.data AS vote_total, s13.data AS vote_user_total, s14.data AS channel FROM content LEFT JOIN content s1 ON s1.field_id=69 AND s1.row_id = content.row_id LEFT JOIN content s2 ON s2.field_id=70 AND s2.row_id = content.row_id LEFT JOIN content s3 ON s3.field_id=71 AND s3.row_id = content.row_id LEFT JOIN content s4 ON s4.field_id=72 AND s4.row_id = content.row_id LEFT JOIN content s5 ON s5.field_id=73 AND s5.row_id = content.row_id LEFT JOIN content s6 ON s6.field_id=74 AND s6.row_id = content.row_id LEFT JOIN content s7 ON s7.field_id=76 AND s7.row_id = content.row_id LEFT JOIN content s8 ON s8.field_id=84 AND s8.row_id = content.row_id LEFT JOIN content s9 ON s9.field_id=129 AND s9.row_id = content.row_id LEFT JOIN content s10 ON s10.field_id=116 AND s10.row_id = content.row_id LEFT JOIN content s11 ON s11.field_id=118 AND s11.row_id = content.row_id LEFT JOIN content s12 ON s12.field_id=120 AND s12.row_id = content.row_id LEFT JOIN content s13 ON s13.field_id=121 AND s13.row_id = content.row_id LEFT JOIN content s14 ON s14.field_id=125 AND s14.row_id = content.row_id WHERE content.app_id = 11 AND unix_timestamp(s6.data)-unix_timestamp('2003-10-23 23:59:59') < 0 GROUP BY row_id ORDER BY last_update desc LIMIT -1
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
