On Wednesday 21 March 2007 16:26, Elazar Leibovich wrote: > This is not the problem. The problem is that running Queries like: > SELECT SQL_CALC_FOUND_ROWS c.cat_ID FROM > posts AS l,post2cat AS a2c, > categories AS c > WHERE (date>2006-01-01 AND date<2006-01-02) AND(c.cat_ID=a2c.category_id > AND l.article_id=a2c.post_id) > AND (c.category_name="cat1" OR category_name="cat2") > GROUP BY l.id > HAVING count(a2c.rel_id)=%s > LIMIT 10;
Ok, what i see here (again without knowing the internals of MYSQL), is that you have 3 tables posts and post2cat and categories. you perform a join: (categories join post2cat) join posts. Naturally all keys mentioned in the join should have PRIMARY index otherwise it will be real slow. Next, i see a further constraining clause of date>...<... So put a primary index there on the date clause and on the table the date attribute belongs to you will have to drop the old index on the id field and make it a secondary index (no choice but it is better). Another thing you can do is to split some tables by date. If most queries are for certain dates then there is no reason to do queries on whole tables. If you want to further make it run a lot faster you will have to say what is the rate of insertions into the tables. What you can do in situations where you have huge data is to use a technique called "Materialized Views". I expect you know what a view is. Well, a materialized view is just a view that is saved to a table and is updated each time there is an insertion (or selection, depends on your needs). This way, even if it takes 10 minutes to update the view. If the insertion only happens 3 times a day, it is worth it. Other than that you will have to redesign your database/your software logic to work only on the relevant sets of data. > Takes about 10 minutes to complete. I want to run many such queries, and > I'm not sure if I can optimize it, or that since the DB is huge, I've met > the SQL limit and I should start looking in other directions (reducing logs > resolution etc.). > Will the fact I'll have no TEXT columns speed things up? Will removing all > columns except of id from the giant posts table throttle things up > significantly? -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ================================================================To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
