Forum Moderators: open
There are indexes on: with cardility
ID PRIMARY 1193287
rss_id INDEX 5473
pub_date INDEX 596643
hits INDEX 389
titleindx INDEX 1193287
feed_url INDEX 1193287
EXPLAIN SELECT id, feed_title, feed_detail, feed_image, pub_date
FROM link_feeds
WHERE rss_id =2
ORDER BY id DESC
LIMIT 25
[ Edit ] [ Skip Explain SQL ] [ Create PHP Code ]
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE link_feeds ref rss_id rss_id 2 const 4105 Using where; Using filesort
I am not sure, your help is greatly appreciated thanks!
This can replace your current index on rss_id, as the rss_id prefix of rss_id,id can be used in any case where rss_id is required.
$res1=mysql_query('select id, feed_title, feed_detail, feed_image, pub_date from link_feeds where rss_id=' . $id . ' order by id desc' . $nrec . '');
which query turns into:
select id, feed_title, feed_detail, feed_image, pub_date from link_feeds where rss_id=249 order by id desc limit 0,8
On first load on page I get slow time...
after
$res1=mysql_query('select id, feed_title, feed_detail, feed_image, pub_date from link_feeds where rss_id=' . $id . ' order by id desc' . $nrec . '');
=
Processed:0.393Memory:389920
other queries, few diff stuff after, final total loading time for box
all the way through the while($row1=mysql_fetch_assoc($res1))
with above time is
=
Processed:0.395Memory:389920
----
there is about 16 diff boxes so it matters...
If you refresh the page they all load in .001...I do the query by itself always get .001 or less....
What am I doing wrong? Thanks.
let's say you wanted to order by rss_id, with a secondary sort on id. That is, you've got a bunch of records for each rss_id, and you want to see a list ordered by rss_id, but within the group of records with the same rss_id, you want to sort by id.
This is a multi-level sort.
In order to do this efficiently, you need to have a composite key on rss_id, id. Simply having separate keys on rss_id and id doesn't really help.
It doesn't matter that the fields contain different information, or even different data types.
The composite key is "as if" you had a key on a column that contained the data in rss_id with the data in id appended to it.
You now have a key that will give you exactly the order you need.
Your case of doing a "where" on rss_id and an order on id is just a degenerate case of "order by rss_id, id".
Once you have a composite key on "rss_id, id", you can drop the key you have on rss_id, because the composite key can serve the same purpose. MySQL knows to ignore the second part of the key if you select or order on just rss_id. So, your old key on rss_id alone is redundant and unnecessary.
I'd suggest picking up a good book on MySQL (I like MySQL by Paul DuBois) and begin at the beginning. Or a good book on relational databases in general.
I have
Keyname Type Cardinality Field
id UNIQUE 1250777 id rss_id
created
but I need more info than just id, rssid, and would need to order only by id, not rss_id..
thanks..
I have
Keyname Type Cardinality Field
id UNIQUE 1250777 id rss_id
Nope. You got it backwards. Needs to be rss_id,id NOT id,rss_id.
You are selecting records where rss_id = some value - then sorting by id.
So, rss_id has to come first in the compound key.
but I need more info than just id, rssid
No problem. This doesn't affect your ability to select any or all of the columns.
and would need to order only by id, not rss_id..
This is what you are missing: IN EFFECT, you are ordering by rssid,id. Only you are only interested in a subset of the values, those with a particular rssid.
Imagine that there were no WHERE clause in SQL. So, let's say you print the entire database, and pick-out what you are interested in visually.
How would you do it, assuming the ogres haven't also taken away the SQL ORDER BY clause?
You'd have to sort the table by rss_id,id, then scan down with your eyes to the rss_id you are interested in. Draw a line. Now scan down and find the next rss_id. Draw a line. Your result is the rows between the lines.
As far as indexing goes, "ORDER BY rss_id_id" and "WHERE rss_id = <some value> ORDER BY id" are one and the same problem.
thanks..
You can drop the index on rss alone. No harm in leaving it, but it's redundant - just wasting space.
I'd leave the index on id, as it may be useful in other queries.
Here's a hint: often, when I have a table that contains columns with two different kinds of ids, (typically representing the primary keys of two different tables) it's useful to have compound keys on the two ids in both orders.
That is, in your case:
rss_id, id and
id,rss_id
If you did this, of course, you would then drop your index on id.
If somebody showed me this schema, and didn't tell me how they were going to query the database, I would add both indices shown above. That is, these two compound indices have great utility for accessing this table, and so I would just automatically create them.
The following indices are unnecessary for this particular query:
pub_date INDEX 596643
hits INDEX 389
titleindx INDEX 1193287
feed_url INDEX 1193287
But I don't know if you are making other queries where they would be useful.
If you have a WHERE clause or ORDER BY that uses any of these columns, then these indices might be useful. But I would look, again, for situations where you use some combination of columns in the ORDER BY and WHERE. If you do, then you need compound keys in those cases.
