fenway

Sorry, my bad, the parens should around topic_id, not USING... like earlier in the query: SELECT cat.category_id , t.topic_id , c.created_on FROM forum_categories AS cat LEFT JOIN forum_topics AS t USING ( category_id ) LEFT JOIN ( SELECT topic_id , MAX(created_on) AS created_on FROM forum_comments WHERE confirmed = '1' GROUP BY topic_id ) AS c USING ( topic_id )

Links and filepaths have nothing to do with databases.

Doesn't explain why you're not using an ENUM. Does your query have a space beween "AS c" and "( USING" --- mine does, but your post seems to suggest otherwise?

BTW, why is confirmed storing "1" but in a field of TEXT?!?!? And you have almost no indexes whatsoever -- where are the ones for the JOINs??? This is the typical groupwise-max issue that comes up time & time again. First, start with gettting the most recent comments: SELECT topic_id , MAX(created_on) FROM forum_comments WHERE confirmed = '1' GROUP BY topic_id Normally, we would need to join this back to the comments table to get the rest of the fields from this table... but you're not using them, so we can skip to the main join: SELECT cat.category_id , t.topic_id , c.created_on FROM forum_categories AS cat LEFT JOIN forum_topics AS t USING ( category_id ) LEFT JOIN ( SELECT topic_id , MAX(created_on) AS created_on FROM forum_comments WHERE confirmed = '1' GROUP BY topic_id ) AS c ( USING topic_id )

Try: WHERE yourField LIKE 'A%'

How?

What are you trying to do?

Why not just write a custom mysql_query() functions that simply dumps out the query text to a file?

All of Shakespeare's works COMBINED is only 5.32 MB....

I like SQLylog.

And assuming you're the only one with access to the box, you won't care that your user/pass will show up in logs / top / etc.

I don't see a flag field anywhere in your tables... and storing full paths and html like that is going to get you into trouble.

I see no reason why that wouldn't work... the only reason I prefer cross join is that I have the conditions in the where clause, which I find more explicit when I'm joining for speed instead of "by design", if you know what I mean. Besides, what error do you get?

It's not "my" idea at all... just look at some EXPLAIN output and you'll see... but generally speaking, HAVING is bad.

Yup... it's still much faster.

Not million, BILLION... and it will never happen.

Why two queries? Same variable? What are you trying to do again?

Unless you're in strict mode, then it will barf.

That will work, but it's going to be very bad for index usage (check EXPLAIN) what if 90% of the rows have either, but only 2% have both? Also, depending on what version of mysql, index_merge many not be an option, so using IN() isn't great either. It's better to simply to simply join the table to itself and see if you get any rows back: SELECT t1.id FROM your_table as t1 CROSS JOIN your_table as t2 USING ( id ) WHERE t1.color = 'blue' and t2.color = 'purple'

I don't need to see the page or the code... just the query that you're currently running.

That doesn't make any sense... a field can't have multiple values simultaneously!

That's probably because of that strange code you're using. Check mysql_num_rows() after the query() call.

Something like: SELECT i.* , u.time AS title_time , IFNULL( u.time, i.original ) AS sort_date FROM items AS i LEFT JOIN ( SELECT id, MAX(`time`) AS time FROM `updates` GROUP BY id ) AS u ON ( u.id = i.id ) WHERE i.group_id` IN ( '111', '112' ) ORDER BY IF(`title_time` IS NOT NULL, `title_time`, `items`.`original`) as `sort_date` FROM `items` WHERE ( `items`.`group_id` IN ( 111, 112) ) ORDER BY IFNULL( u.time, i.original ) DESC LIMIT 9

Potentially up to 3 times less.

It's impossible that the same query run on the same database gives different results. I'd confirm it's actually the same query, and check the count of the records.