MySQL Query Optimization?

This query may be optimized, depending on which condition is more selective: tags. Name = 'iphone4 or article. Date > '2010-02-07 13:25:46 If there are less articles tagged iphone than those posted after Feb 7 then your original query is nice If there are many articles tagged iphone but few those posted after Feb 7 then this query will be more efficient: SELECT article.Id, users.

Username, article. Title FROM tags JOIN article ON article.Id = tags. Article_id AND article.

Status = '1' AND article. Section = 'mobile' AND article. Date > '2010-02-07 13:25:46' JOIN users ON users.

Id = article. Author_id WHERE tags. Name = 'iphone4' ORDER BY tags.

Article_date DESC, tags. Article_id DESC Note that the ORDER BY condition has changed. This may or may not be what you want, however, generally the orders of id and date correspond to each other If you really need your original ORDER BY condition you may leave it but it will add a filesort (or just revert to your original plan) In either case, create an index on article (status, section, date, id).

This query may be optimized, depending on which condition is more selective: tags. Name = 'iphone4' or article. Date > '2010-02-07 13:25:46' If there are less articles tagged iphone than those posted after Feb 7, then your original query is nice.

If there are many articles tagged iphone, but few those posted after Feb 7, then this query will be more efficient: SELECT article. Id, users. Username, article.

Title FROM tags JOIN article ON article. Id = tags. Article_id AND article.

Status = '1' AND article. Section = 'mobile' AND article. Date > '2010-02-07 13:25:46' JOIN users ON users.Id = article.

Author_id WHERE tags. Name = 'iphone4' ORDER BY tags. Article_date DESC, tags.

Article_id DESC Note that the ORDER BY condition has changed. This may or may not be what you want, however, generally the orders of id and date correspond to each other. If you really need your original ORDER BY condition you may leave it but it will add a filesort (or just revert to your original plan).

In either case, create an index on article (status, section, date, id).

The query should output all open articles about 'iphone' from the last month. So the only query you are going to run on this data uses the tag and the date. You've got a index for the tag in the tags table, but the date is stored in a different table (article - you're a bit inconsistent with your naming schema).

Adding an index on the article table using date would be no benefit at all. Using id,date (in that order) would help a little - but really the date needs to be denormalised into the tags table to get the query running really fast. Unless you're regularly moving around bulk data sets - just add a datetime column with a default of the current timestamp to the tags table.

I expect that you may be wanting to interact with the data in lots of other ways - really you should set a low (no? ) threshold for slow query logging then analyse the resulting data to identify where you're performance problems are (try looking at the queries with the highest values for duration^2*frequency first). There's a script at the URL below which is useful for this analysis: retards.org/projects/mysql.

You could index the additional fields in article that you are referencing in your select statement. In this case, I would suggest you create an index in article like this: CREATE INDEX article_idx ON article (author_id, status, section, date); Creating that index should speed up your query depending on how many overall records you are dealing with. From my understanding, properly creating indexes involves looking at the queries you've written and indexing the columns that are a part of your where clause.

This helps the query optimizer better process the query in general. That does not mean create an index on each individual column, however, as its both inefficient to do so and ineffective. When possible, create multiple column indexes that represent your select statement.

The only query usef_ksa has mentioned is using keyword and article date - that query won't the index you suggest. – symcbean Feb 10 at 12:59 In the original question the where clause is specifically looking at all 4 of those fields in article. Not all 4 are joined to another table, but all 4 are used in the actual query.

Why wouldn't it use that index in the query? It indexes the remaining fields being queried that are not a part of the primary key. – dmcnelis Feb 10 at 15:07.

This query may be optimized, depending on which condition is more selective: tags.name = 'iphone4' or article. Date > '2010-02-07 13:25:46.

So the only query you are going to run on this data uses the tag and the date. You've got a index for the tag in the tags table, but the date is stored in a different table (article - you're a bit inconsistent with your naming schema). Adding an index on the article table using date would be no benefit at all.

Using id,date (in that order) would help a little - but really the date needs to be denormalised into the tags table to get the query running really fast.

What about using the IN keyword instead of multiple OR . Also, you don't need to specify AND segments. Segmentstart > "00:00:00" since you have already provided a condition segments.

Segmentstart >= TIME(DATE_ADD(now(), INTERVAL "07:00" HOUR_MINUTE)) which is greater than "00:00:00" . Lastly, indexing your keys will be a good idea to optimize execution.

If you have an IDs column and the gaps between the elements are not huge (not too many elements were removed, otherwise some elements will be selected more often) then try this query.

Firstly, make sure your table is properly indexed. Does it have a primary key? Is languageId an index?

Make sure it is.

Thanks guys & sorry Marcelo it's my first time on Stackoverflow.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.