Hmmm, if I execute this 3 queries at any time in any order I get the same execution time.
Yes, explain... explain select artists.name , cds.title , tracks.title from artists, tracks, cds where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid and MATCH (artists.name) AGAINST ('madonna') and MATCH (cds.title) AGAINST ('music') and MATCH (cds.title) AGAINST ('mix') and MATCH (cds.title) AGAINST ('2001') | table | type | possible_keys | key | key_len | ref | rows | Extra | artists | fulltext | PRIMARY,name | name | 0 | | 1 | Using where | | tracks | ref | PRIMARY,artistIndex | artistIndex | 5 | artists.artistId | 27 | Using where | | cds | fulltext | PRIMARY,fulltext_title | fulltext_title | 0 | | 1 | Using where | explain select artists.name , cds.title, tracks.title from artists, tracks, cds where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE) and MATCH (cds.title) AGAINST ('"music mix 2001"' IN BOOLEAN MODE) | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+----------+------------------------+----------------+-------- -+-- | artists | fulltext | PRIMARY,name | name | 0 | | 1 | Using where | | tracks | ref | PRIMARY,artistIndex | artistIndex | 5 | artis ts.artistId | 27 | Using where | | cds | fulltext | PRIMARY,fulltext_title | fulltext_title | 0 | | 1 | Using where | explain select artists.name , cds.title, tracks.title from artists, tracks, cds where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid and artists.name like '%madonna%' and cds.title like '%music mix 2001%' | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+--------+---------------------+-------------+---------+------ +---------+--------+---------------------+-------------+---------+---- | artists | ALL | PRIMARY | NULL | NULL | NULL | 23806 | Using where | | tracks | ref | PRIMARY,artistIndex | artistIndex | 5 | artists.artis tId | 27 | Using where | | cds | eq_ref | PRIMARY | PRIMARY | 4 | tracks.cdId | 1 | Using where | -----Original Message----- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2003 16:38 To: Uros Kotnik Cc: [EMAIL PROTECTED] Subject: Re: Once again, three queries, same result, huge speed difference You need to take cache into consideration when doing your testing. Both MySQL cache and the OS cache. That means rebooting between each query that you run to clear the database and OS cache. -or- Run each query 3 or 4 times (or 5, or even 10) consecutively and either take the average or the fastest. Doing it this way will make sure that the cache is used equally for all queries. You should also do and EXPLAIN to see how MySQL is executing each query. On Dec 4, 2003, at 5:35 AM, Uros Kotnik wrote: > Same result but the speed difference is quite a different, why is that > ? > > This is only on test DB, I didn't try it on real life DB where I have > ~14 mil. rows in tracks table. > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]