Once again, three queries, same result, huge speed difference
I posted this few days ago, but with no answer, also posted it to
benchmark list..
Executing this SQL, takes ~5 sec.
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')
and this, ~40 sec.
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)
and executing this takes less than 1 sec.
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%'
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.
Regards
Re: Once again, three queries, same result, huge speed difference
On Thu, 4 Dec 2003, Uros Kotnik wrote:
I posted this few days ago, but with no answer, also posted it to
benchmark list..
Executing this SQL, takes ~5 sec.
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')
and this, ~40 sec.
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)
and executing this takes less than 1 sec.
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%'
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.
Regards
Can you post EXPLAIN SELECT of those queries as well, please?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Once again, three queries, same result, huge speed difference
Another thing that I noticed is :
This query takes less than sec :
SELECT artists.name, cds.title, tracks.title
FROM artists, tracks, cds
WHERE artists.artistid = tracks.artistid AND cds.cdid = tracks.cdid AND
MATCH ( name )
AGAINST ( 'madonna' )
But when I add one more AND it takes more than 15 min.
SELECT artists.name, cds.title, tracks.title
FROM artists, tracks, cds
WHERE artists.artistid = tracks.artistid AND cds.cdid = tracks.cdid AND
MATCH ( name )
AGAINST ( 'madonna' )
AND
MATCH ( cds.title )
AGAINST ( 'music' )
-Original Message-
From: Tobias Asplund [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 11:50
To: Uros Kotnik
Cc: [EMAIL PROTECTED]
Subject: Re: Once again, three queries, same result, huge speed
difference
On Thu, 4 Dec 2003, Uros Kotnik wrote:
I posted this few days ago, but with no answer, also posted it to
benchmark list..
Executing this SQL, takes ~5 sec.
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')
and this, ~40 sec.
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)
and executing this takes less than 1 sec.
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%'
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.
Regards
Can you post EXPLAIN SELECT of those queries as well, please?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
RE: Once again, three queries, same result, huge speed difference
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]
Re: Once again, three queries, same result, huge speed difference
It's not the order in which you execute the queries, it's how many
time. Execute the first one 5 times, then the second one 5 times, then
the third one 5 times. See if the times are different between each of
the 5 runs for each query.
Also, you could try reordering your query. Perhaps something like
select fields from cds, artists, tracks...
On Dec 4, 2003, at 10:45 AM, Uros Kotnik wrote:
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 |
--
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]
