Selon Michael Stassen <[EMAIL PROTECTED]>: > The PK is not used, nor should it be. Look at the EXPLAIN output.
Hi, i hate the confusion people do between the primary key and the automatic index on the PK. Primary key is a generic concept for all databases. It assumes unicity and managed data insertion. It's a physical notion. When i say using PK, i mean using PK, not the PK index. This is a sequential reading of all data pages, one by one. I always here saying, it's a full table scan ? What does this mean ? this means that the rdbms doesn't use a rowid found in the index leaf pages to access randomly (hash) to data pages. mysql> create table ordered (a int auto_increment primary key,b varchar(10)); mysql> insert into ordered(b) values(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); mysql> select * from ordered; +----+------+ | a | b | +----+------+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | NULL | | 5 | NULL | | 6 | NULL | | 7 | NULL | | 8 | NULL | | 9 | NULL | | 10 | NULL | +----+------+ 10 rows in set (0.02 sec) This is an FTS (using the primary key, not the PK Index). Or if you want, there no index fast scan (or full scan) before data reading. if i'm wrong, execuse my ugnorance. That can be. Mathias > > Let me rewrite the query with explicit joins, and reformat it for > readability: > > SELECT <long list of columns> > FROM qb_test_result > JOIN qb_test_result_details > ON qb_test_result.id = qb_test_result_details.resultId > JOIN qb_test > ON qb_test_result.testId = qb_test.id > JOIN qb_question > ON qb_test_result_details.questionId = qb_question.id > WHERE qb_test.companyId =1 > AND qb_test.author = '2'; > > There are no restrictions on rows from qb_test_result in the WHERE clause, so > every row matches. Hence, a full table scan of qb_test_result is required, > and no index will be used. Adding indexes to qb_test_result won't help. > > There is an index on (companyId,author) in qb_test which might have helped, > but apparently the optimizer decided that there would be more matching rows > in > qb_test than the total number of rows in qb_test_result. I do notice that > author is an INT, but the query compares it to the string '2'. I doubt that > confused the optimizer, but it should be fixed anyway. Change that > comparison to > > AND qb_test.author = 2; > > Michael > > > Selon Sajith A <[EMAIL PROTECTED]>: > > > >>I was trying to analyze a query that was taking almost 4 seconds to > >>execute. While trying to create additional indexes - found that the > >>query is not using any index from table qb_test_result . The type > >>returned is ALL for qb_test_result . > >> > >>I have given the tables and query below. It would have been a help if > >>some one could throw some light on why this is behaving so.. I tried > >>to read the mysql manual and follow the > >>steps given there. > >> > <snip> > >> > >>EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS > >>testId, qb_test.title testName, qb_question.marks, qb_test.passrate, > >>qb_test_result.marks testMark, qb_test_result.percentage > >>testPercentage, qb_test_result.startTime, qb_test_result.endTime, > >>qb_test_result.status > >>FROM qb_test_result, qb_test_result_details, qb_test, qb_question > >>WHERE qb_test_result.id = qb_test_result_details.resultId > >>AND qb_test_result.testId = qb_test.id > >>AND qb_test.companyId =1 > >>AND qb_test.author = '2' > >>AND qb_test_result_details.questionId = qb_question.id > >> > > > > > +------------------------+--------+-----------------------+----------+---------+-----------------------------------+------+-------------+ > > > >>| table | type | possible_keys | key | > >>key_len | ref | rows | Extra | > >> > > > > > +------------------------+--------+-----------------------+----------+---------+-----------------------------------+------+-------------+ > > > >>| qb_test_result | ALL | PRIMARY,testStudent | NULL | > >> NULL | NULL | 2494 | | > >>| qb_test_result_details | ref | resultId | resultId | > >> 4 | qb_test_result.id | 45 | | > >>| qb_test | eq_ref | PRIMARY,companyAuthor | PRIMARY | > >> 2 | qb_test_result.testId | 1 | Using where | > >>| qb_question | eq_ref | PRIMARY | PRIMARY | > >> 4 | qb_test_result_details.questionId | 1 | | > >> > > > > > +------------------------+--------+-----------------------+----------+---------+-----------------------------------+------+-------------+ > > > >> > >> > >>Thank you > >>Sajith A > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
