Dave,
It's best to keep threads on the list. On average, you'll get better and faster responses when all the experts can see your question. Also, others can benefit from the answers when they are on the list.
See my comments below.
Critters wrote:
Thanks for your response, here is some more detail.
The table is like this: | id | name | dateTime | award | FP | ip | doodleid | votes | score | voted | compid | viewed | views | doodle 11,000 records 380 meg id, award, fp, doodleid, compid are indexed
Are these all single column indexes, or did you use multiple column indexes? Depending on your typical queries, multiple column indexes may help.
they are all int except "name" and "voted" which are varchar 255 length and doodle is longtext
ANALYZE TABLE doodlebug +-------------------------------+---------+-----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------------------+---------+-----------+----------+ | desktopcreatures2.doodlebug | analyze | status | OK | +-------------------------------+---------+-----------+----------+
SELECT id FROM `doodlebug` WHERE compid = 34 12 rows in 0.33sec
SELECT id, name FROM `doodlebug` WHERE compid = 34 12 rows in 29.67sec
With an index on compid, these should have been about the same time. The second one definitely looks like a full-table scan, though even the first seems slow to me.
EXPLAIN SELECT id FROM doodlebug where compid = 34 +-----------+-------+---------------+---------+---------+--------+-------+-- ------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+-------+---------------+---------+---------+--------+-------+-- ------------------------+ | doodlebug | index | [NULL] | PRIMARY | 20 | [NULL] | 10977 | Using where; Using index | +-----------+-------+---------------+---------+---------+--------+-------+-- ------------------------+
Does this shead any light on the problem?
Yes. EXPLAIN says the only index it can use is the PRIMARY index. The key_len is 20, which tells me that the PRIMARY index is a multiple column index. The number of rows to be examined is 10,977, the whole table, which tells me that compid is not the first column in the index. I'm guessing you have something like PRIMARY KEY (id, award, fp, compid, doodleid). A multiple column index can be used to satisfy WHERE clauses using any left-most combination of the columns in the index. Thus, with only one column in the WHERE clause, it would need to come first in the index for the index to be used to satisfy the WHERE clause.
The index is still being used for this query, however. Because you only want the value of id, and id is in the index, mysql reasons that it would be quicker to scan the relatively small index for the matching rows than to scan the much larger table. That is, you got a full-indexx scan. That's why there's a difference between this and your second query,
SELECT id, name FROM `doodlebug` WHERE compid = 34
In that case, name is not part of the index, so the full table is scanned. That takes a while, as you saw.
The solution is to add an index which starts with compid. Whether that should be a single or multiple column index depends on what other indexes you have and what your typical queries look like.
You should read through the manual sections "Multiple-Column Indexes" <http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html> and "How MySQL Uses Indexes" <http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html>. Then, if you still have questions, feel free to post them. It would probably help in that case to include the output of
SHOW CREATE TABLE doodlebug;
Along with a representative sample of typical queries.
-- Dave
Then Critters wrote:
Even thought I am only asking for the "id" and "name" fields is the speed affected by the "doodle" field which is about 90k for each record and makes up the majority of the tables 380meg? If i moved the "doodle" field to another table that just had "id", "doodleid", "doodle" would that make the current table faster? -- Dave
Probably. There are 2 factors here: As your current query does a full-table scan, table size is definitely an issue. This should be fixed by the addition of an index on (or starting with) compid. You may find that gives acceptable results. If not, consider factor 2.
The second factor is the nature of the rows in your table. Fixed length rows are faster than variable length rows in MyISAM tables (the default). With fixed-length rows, you can calculate the starting position in the file of any row. With variable-length rows you have to guess and search. To make your table fixed-length, you'd have to do something with the 3 variable-length columns, name, doodle, and voted. You've already hit the solution for the text column, doodle -- move it to a separate table. Your name column could probably be changed to a CHAR(N). You would choose a value of N large enough to hold every anticipated name, but no larger. (If you guess wrong, you can increase it later.) You'd waste some space, but gain some speed, so long as you end up with fixed-length rows. I'm not sure what you have in voted, so I can't really guess whether you could make it fixed-length or need to move it to the second table.
Another option would be to make this an InnoDB table. The fixed-length vs. variable-length speed difference is much less of an issue in InnoDB tables.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
