Re: Speed difference between boolean full-text searches and full-text searches
Hi!
Sorry for delayed answer - I was on vacations...
On Dec 08, Uros Kotnik wrote:
> OK, I will give you more details.
>
> Table CDS, have 1,053,794 rows, FT index on title,
> Data 67,646 KB, Index 70,401 KB
>
> Table ARTISTS, Rows 292,330, FT on name,
> Data 8,096 KB
> Index 17,218 KB
>
> Table TRACKS, rows 13,841,930, FT on title
> Data 625,360 KB
> Index 646,672 KB
>
> ft_min_word_len = 3
> key_buffer_size 786432000
>
> Explain for both SQLs gives same info :
>
> table type possible_keys key key_len ref rows Extra
> artists fulltext PRIMARY,ft_name ft_name 0 1 Using where
> cds fulltext PRIMARY,artistIndex,ft_title ft_title 0 1 Using where
> tracks ref PRIMARY,artistIndex PRIMARY 4 cds.cdId 13 Using where
>
> Time for first SQL : 21 sec.
> SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
> WHERE artists.artistid = cds.artistid AND 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)
>
> Time for second SQL : < 1 sec.
> SELECT artists.name, cds.title, tracks.title
> FROM artists, cds, tracks
> WHERE artists.artistid = cds.artistid AND 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' )
Assuming,
SELECT @N=COUNT(*) FROM cds WHERE MATCH title AGAINST ('music');
SELECT @M=COUNT(*) FROM cds WHERE MATCH title AGAINST ('mix');
SELECT @K=COUNT(*) FROM cds WHERE MATCH title AGAINST ('2001');
SELECT @L=COUNT(*) FROM cds WHERE MATCH title AGAINST ('+music +mix +2001' IN BOOLEAN
MODE);
The first query will do N+M+K index lookups and read L rows from the
disk.
The second query will do N index lookups and read N rows from the disk.
Thus, if (N+M+K) is much greater than N (it usually is)
and L is close to N (it is usually not), than first query should be much
slower. Typically L is less than min(N,M,K), thus the goal is to reduce
the number of row reads.
I agree this optimization is not the best for all situations,
optimizing this type of queries is in the todo.
btw, it could be that my guess about the slowness was wrong :)
Compare these N,M,K,L numbers yourself.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed difference between boolean full-text searches and full-text searches
OK I tried this, '+music +mix +2001' instead of this '"music mix 2001"'
IN BOOLEAN MODE and the SQL time is the same ~21 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
and
MATCH (cds.title) AGAINST ('+music +mix +2001' IN BOOLEAN MODE) and
artists.artistid = cds.artistid AND artists.artistid = tracks.artistid
AND cds.cdid = tracks.cdid
Do you have some explanation, why is this so much slower than this :
SELECT artists.name, cds.title, tracks.title
FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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' )
Regards
-Original Message-
From: Chuck Gadd [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 21:50
To: Uros Kotnik; [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Uros Kotnik wrote:
> It makes sense, but Sergei G. said :
> "And are you sure the numbers are correct, the first query - the one
> without "IN BOOLEAN MODE" - is faster ? I would expect the opposite."
>
> I guess that for my DB I can't expect satisfied "in boolena mode"
times
> ?
> But also when searching without "in boolean mode" and include search
> criteria from TRACKS table, 13,841,930 rows , like "AND MATCH (
> tracks.title) AGAINST ('remix')"
> I get ~10 sec. times.
> Am I doing something wrong or this results are correct for this amount
> of data, I would be satisfied with 0.5 - 1 sec. times
If I'm not mistaken, IN BOOLEAN MODE simply changes the parser
logic. It tells MySql to process the "special" characters, like
+-*"". I don't think it's the IN BOOLEAN MODE that is causing
the slow query, but the fact that you are looking for the phrase.
If you were to do
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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)
Then you'd probably still get the fast search time, since the query
simply requires all three words. MySql can resolve this just using
the index.
In your example, the BOOLEAN MODE for
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
isn't doing anything special, since you aren't using any
special chars to modify the search expression.
--
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: Speed difference between boolean full-text searches and full-text searches
OK I tried this, so '+music +mix +2001' instead of this '"music mix
2001"' and the SQL time is the same ~21 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
and MATCH (cds.title) AGAINST ('+music +mix +2001' IN BOOLEAN MODE)
and artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid
-Original Message-
From: Chuck Gadd [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 21:50
To: Uros Kotnik; [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Uros Kotnik wrote:
> It makes sense, but Sergei G. said :
> "And are you sure the numbers are correct, the first query - the one
> without "IN BOOLEAN MODE" - is faster ? I would expect the opposite."
>
> I guess that for my DB I can't expect satisfied "in boolena mode"
times
> ?
> But also when searching without "in boolean mode" and include search
> criteria from TRACKS table, 13,841,930 rows , like "AND MATCH (
> tracks.title) AGAINST ('remix')"
> I get ~10 sec. times.
> Am I doing something wrong or this results are correct for this amount
> of data, I would be satisfied with 0.5 - 1 sec. times
If I'm not mistaken, IN BOOLEAN MODE simply changes the parser
logic. It tells MySql to process the "special" characters, like
+-*"". I don't think it's the IN BOOLEAN MODE that is causing
the slow query, but the fact that you are looking for the phrase.
If you were to do
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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)
Then you'd probably still get the fast search time, since the query
simply requires all three words. MySql can resolve this just using
the index.
In your example, the BOOLEAN MODE for
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
isn't doing anything special, since you aren't using any
special chars to modify the search expression.
--
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: Speed difference between boolean full-text searches and full-text searches
Uros Kotnik wrote:
It makes sense, but Sergei G. said :
"And are you sure the numbers are correct, the first query - the one
without "IN BOOLEAN MODE" - is faster ? I would expect the opposite."
I guess that for my DB I can't expect satisfied "in boolena mode" times
?
But also when searching without "in boolean mode" and include search
criteria from TRACKS table, 13,841,930 rows , like "AND MATCH (
tracks.title) AGAINST ('remix')"
I get ~10 sec. times.
Am I doing something wrong or this results are correct for this amount
of data, I would be satisfied with 0.5 - 1 sec. times
If I'm not mistaken, IN BOOLEAN MODE simply changes the parser
logic. It tells MySql to process the "special" characters, like
+-*"". I don't think it's the IN BOOLEAN MODE that is causing
the slow query, but the fact that you are looking for the phrase.
If you were to do
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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)
Then you'd probably still get the fast search time, since the query
simply requires all three words. MySql can resolve this just using
the index.
In your example, the BOOLEAN MODE for
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
isn't doing anything special, since you aren't using any
special chars to modify the search expression.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed difference between boolean full-text searches and full-text searches
It makes sense, but Sergei G. said :
"And are you sure the numbers are correct, the first query - the one
without "IN BOOLEAN MODE" - is faster ? I would expect the opposite."
I guess that for my DB I can't expect satisfied "in boolena mode" times
?
But also when searching without "in boolean mode" and include search
criteria from TRACKS table, 13,841,930 rows , like "AND MATCH (
tracks.title) AGAINST ('remix')"
I get ~10 sec. times.
Am I doing something wrong or this results are correct for this amount
of data, I would be satisfied with 0.5 - 1 sec. times
-Original Message-
From: Chuck Gadd [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 13:17
To: Uros Kotnik; [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Uros Kotnik wrote:
> Time for first SQL : 21 sec.
> SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
> WHERE artists.artistid = cds.artistid AND 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)
In this case, it cannot resolve the query JUST using indexes.
After finding all records in the index where artists.name matches
madonna and title contains all the words "music", "mix", "2001",
then it must retrieve each record, and examine the title field to
see if the three words are found together in the phrase.
In your other example, it only needs to use the fulltext indexes
to know which records satisfy your query, resulting in MUCH
faster query time.
--
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: Speed difference between boolean full-text searches and full-text searches
Uros Kotnik wrote:
Time for first SQL : 21 sec.
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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)
In this case, it cannot resolve the query JUST using indexes.
After finding all records in the index where artists.name matches
madonna and title contains all the words "music", "mix", "2001",
then it must retrieve each record, and examine the title field to
see if the three words are found together in the phrase.
In your other example, it only needs to use the fulltext indexes
to know which records satisfy your query, resulting in MUCH
faster query time.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed difference between boolean full-text searches and full-text searches
OK, I will give you more details.
MySQL ver. : 4.0.16
CPU : 2xCelleron 1000 & 1GB RAM
Table CDS, have 1,053,794 rows, FT index on title,
Data 67,646 KB, Index 70,401 KB
Table ARTISTS, Rows 292,330, FT on name,
Data 8,096 KB
Index 17,218 KB
Table TRACKS, rows 13,841,930, FT on title
Data 625,360 KB
Index 646,672 KB
ft_min_word_len = 3
key_buffer_size 786432000
Explain for both SQLs gives same info :
table type possible_keys key key_len ref rows Extra
artists fulltext PRIMARY,ft_name ft_name 0 1 Using where
cds fulltext PRIMARY,artistIndex,ft_title ft_title 0 1 Using where
tracks ref PRIMARY,artistIndex PRIMARY 4 cds.cdId 13 Using where
Last results that I sent are not correct because I forgot to include one
more join, artists.artistid = cds.artistid, bad oversight I know
These are the new results :
Time for first SQL : 21 sec.
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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)
Time for second SQL : < 1 sec.
SELECT artists.name, cds.title, tracks.title
FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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' )
One more thing that I noticed in last SQL, when I change, in FROM
clause, positions of tables like this : FROM artists, tracks, cds,
instead FROM artists, cds, tracks I get time of 1.9 sec. instead < 1
sec. ?
Regards
-Original Message-
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 00:02
To: Uros Kotnik
Cc: [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Hi!
On Nov 27, Uros Kotnik wrote:
> 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')
> limit 1001
>
> 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)
> limit 1001
>
> Same result but the speed difference is quite a different, why is that
?
What does EXPLAIN show for both queries ?
And are you sure the numbers are correct, the first query - the one
without "IN BOOLEAN MODE" - is faster ? I would expect the opposite.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed difference between boolean full-text searches and full-text searches
OK, I will give you more details.
MySQL ver. : 4.0.16
CPU : 2xCelleron 1000 & 1GB RAM
Table CDS, have 1,053,794 rows, FT index on title,
Data 67,646 KB, Index 70,401 KB
Table ARTISTS, Rows 292,330, FT on name,
Data 8,096 KB
Index 17,218 KB
Table TRACKS, rows 13,841,930, FT on title
Data 625,360 KB
Index 646,672 KB
ft_min_word_len = 3
key_buffer_size 786432000
Explain for both SQLs gives same info :
table type possible_keys key key_len ref rows Extra
artists fulltext PRIMARY,ft_name ft_name 0 1 Using where
cds fulltext PRIMARY,artistIndex,ft_title ft_title 0 1 Using where
tracks ref PRIMARY,artistIndex PRIMARY 4 cds.cdId 13 Using where
Last results that I sent are not correct because I forgot to include one
more join, artists.artistid = cds.artistid, bad oversight I know
These are the new results :
Time for first SQL : 21 sec.
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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)
Time for second SQL : < 1 sec.
SELECT artists.name, cds.title, tracks.title
FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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' )
One more thing that I noticed in last SQL, when I change, in FROM
clause, positions of tables like this : FROM artists, tracks, cds,
instead FROM artists, cds, tracks I get time of 1.9 sec. instead < 1
sec. ?
Regards
-Original Message-
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 00:02
To: Uros Kotnik
Cc: [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Hi!
On Nov 27, Uros Kotnik wrote:
> 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')
> limit 1001
>
> 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)
> limit 1001
>
> Same result but the speed difference is quite a different, why is that
?
What does EXPLAIN show for both queries ?
And are you sure the numbers are correct, the first query - the one
without "IN BOOLEAN MODE" - is faster ? I would expect the opposite.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed difference between boolean full-text searches and full-text searches
Hi!
On Nov 27, Uros Kotnik wrote:
> 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')
> limit 1001
>
> 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)
> limit 1001
>
> Same result but the speed difference is quite a different, why is that ?
What does EXPLAIN show for both queries ?
And are you sure the numbers are correct, the first query - the one
without "IN BOOLEAN MODE" - is faster ? I would expect the opposite.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
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 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]
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
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
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
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]
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
Speed difference between boolean full-text searches and full-text searches
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')
limit 1001
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) limit
1001
Same result but the speed difference is quite a different, why is that ?
Regards
RE: Speed difference
The boxes running the MySQL were isolated from other users so there was no activity on them while I was performing the benchmark tests. There was no tuning - I just installed the MySQL from their binary packages, that is I was using the default config files (hoping that they are the same - I will check that now). One of the tests was done locally on the Solaris box (Ultra 5) giving almost the same result - around 1 min 25 secs. Just a small improvement over the TCP/IP connection. I suppose that the next step is to get the config file to something that will give me better performance on the Solaris box, but as I said there was nothing changed in the configuration of the MySQL. I just used the default configuration setup. _ Nesh, > With all of the benchmark test I have used the same DB structure, > same TABLE structure, and finally same iterations. > > This probably means that Solaris I/O is really poor, or there is some > other explanation (like changing some kernel parameters to get Solaris > working well with DB kind of stuff). > > At the moment I am trying to find out exactly what is the problem. It > should not be MySQL itself because the first 2 boxes have different > results from the last 2 boxes. This leads me to the conclusion that > Solaris is doing something weird with the I/O. Unfortunately, I do not > have Solaris 8 to test MySQL in that environment. > > Note that all the boxes are running the same MySQL version so that > could not be the problem (3.23.47). Interesting statistics! What else were the m/user, m/tasking OpSys doing at the time? How much 'tuning' of the configs/dbs had taken place in each case? Can you compare the results without the 'I/O', by using native-MySQL on each server, locally? Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Speed difference
Nesh, > With all of the benchmark test I have used the same DB structure, same > TABLE structure, and finally same iterations. > > This probably means that Solaris I/O is really poor, or there is some other > explanation (like changing some kernel parameters to get Solaris working > well with DB kind of stuff). > > At the moment I am trying to find out exactly what is the problem. It > should not be MySQL itself because the first 2 boxes have different results > from the last 2 boxes. This leads me to the conclusion that Solaris is doing > something weird with the I/O. Unfortunately, I do not have Solaris 8 to test > MySQL in that environment. > > Note that all the boxes are running the same MySQL version so that could > not be the problem (3.23.47). Interesting statistics! What else were the m/user, m/tasking OpSys doing at the time? How much 'tuning' of the configs/dbs had taken place in each case? Can you compare the results without the 'I/O', by using native-MySQL on each server, locally? Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Speed difference
With all of the benchmark test I have used the same DB structure, same TABLE structure, and finally same iterations. This probably means that Solaris I/O is really poor, or there is some other explanation (like changing some kernel parameters to get Solaris working well with DB kind of stuff). At the moment I am trying to find out exactly what is the problem. It should not be MySQL itself because the first 2 boxes have different results from the last 2 boxes. This leads me to the conclusion that Solaris is doing something weird with the I/O. Unfortunately, I do not have Solaris 8 to test MySQL in that environment. Note that all the boxes are running the same MySQL version so that could not be the problem (3.23.47). Regards, Nesh Nenad Mijailovic Software Engineer IVSTEL Telecommunications Solutions _ -Original Message- From: Sherzod Ruzmetov aka sherzodR [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 5 February 2002 11:14 AM To: Nesh Nenad Mijailovic Cc: '[EMAIL PROTECTED]' Subject: Re: Speed difference : after timing the program that was targeting MySQL on different platforms : the results are (client program running always from the same box that is not : hosting MySQL): : : Windows NT - 25 sec (Intel P-III) : Linux - 27 sec (Intel P-III) : Solaris 2.7 - 1 min 40 sec (Ultra 5) : Solaris 2.7 - 1 min 32 sec (Ultra 10) : : Is there a reasonable explanation why is this happening. : Well, even though you run the same benchmark on the same machine several times, you'll get different results. So we also need to know if adequate iterations have been performed. So the time difference in the first two results aren't that much different at all. The same conclusion is true about the last two, But the slowness of Solaris boxes does impress me. So I believe (i'm not a solaris dude) Ultra is the processor, right? Then we could conclude that pentiums are much more faster then Ultras. Do you aggree? -- Sherzod Ruzmetov <[EMAIL PROTECTED]> http://www.UltraCgis.com, Consultant 989.774.6265 010010100101010101001100 ++ | There is nothing wrong with your tools.| | But we can make a better one. | ++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Speed difference
: after timing the program that was targeting MySQL on different platforms : the results are (client program running always from the same box that is not : hosting MySQL): : : Windows NT - 25 sec (Intel P-III) : Linux - 27 sec (Intel P-III) : Solaris 2.7 - 1 min 40 sec (Ultra 5) : Solaris 2.7 - 1 min 32 sec (Ultra 10) : : Is there a reasonable explanation why is this happening. : Well, even though you run the same benchmark on the same machine several times, you'll get different results. So we also need to know if adequate iterations have been performed. So the time difference in the first two results aren't that much different at all. The same conclusion is true about the last two, But the slowness of Solaris boxes does impress me. So I believe (i'm not a solaris dude) Ultra is the processor, right? Then we could conclude that pentiums are much more faster then Ultras. Do you aggree? -- Sherzod Ruzmetov <[EMAIL PROTECTED]> http://www.UltraCgis.com, Consultant 989.774.6265 010010100101010101001100 ++ | There is nothing wrong with your tools.| | But we can make a better one. | ++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Speed difference
Hi All, I have tried out some performance testing with some heavy SELECT and UPDATES. The database in question has only 500 records but there are lots of SELECTs and UPDATEs and the results are: after timing the program that was targeting MySQL on different platforms the results are (client program running always from the same box that is not hosting MySQL): Windows NT - 25 sec (Intel P-III) Linux - 27 sec (Intel P-III) Solaris 2.7 - 1 min 40 sec (Ultra 5) Solaris 2.7 - 1 min 32 sec (Ultra 10) Is there a reasonable explanation why is this happening. Thanks, Nesh Nenad Mijailovic Software Engineer IVSTEL Telecommunications Solutions - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: beta-log version speed difference ?
On Wed, May 23, 2001 at 07:09:23PM -0700, S A wrote: > > How much if at all slower should a BETA-LOG build of MySQL be than > one that doesn't log ? > > We have on BSD 3.23.26-beta-log which is MUCH slower than 3.23.32 on > Linux. It depends on how IO and CPU bound your system is. It could be marginally slower, or it could be A LOT slower. There's not any good rule of thumb I know of... Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 4 days, processed 33,187,393 queries (80/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
beta-log version speed difference ?
How much if at all slower should a BETA-LOG build of MySQL be than one that doesn't log ? We have on BSD 3.23.26-beta-log which is MUCH slower than 3.23.32 on Linux. - Sam. - Do You Yahoo!? Yahoo! Auctions $2 Million Sweepstakes - Got something to sell?
