Also note that the server is pretty fast, dual 2 GHz with 2GB of RAM. Using the
my-huge.cnf settings.
Also... I forgot to mention [EMAIL PROTECTED] I'm using:
mysql 4.0.20-standard-log Official MySQL RPM
And here is the EXPLAIN on the select portion of the slow query is:
I guess I've never noticed that but why not just select more than one calculation such
as:
SELECT sqrt(col) AS blarg, sqrt(col) + 1 AS order_blarg
FROM table
ORDER BY order_blarg
its a work around but it should work fine
-John
--
MySQL General Mailing List
For list archives:
I'm wondering whether or not I should implement fulltext searching for 2 letter words
within a product database (HP, TV, G5, LG, etc)... My searchable text is currently
about 600Mb and a 3-letter fulltext index is consuming about 420Mb.
Is the trade-off for user convience vs. performance worth
I'm having a bit of a problem with a query that takes a very long time (up to 1
minute) when many matching rows are found.
The tables are all indexed and the explain seems to indicate that mysql is using the
indexes but it is still painfully slow:
mysql SELECT COUNT(TP.thread_id) AS num_posts,
Thanks Shawn, I'm going to give the temporary table idea a try.
I did omit the 'category' table while testing but when I used EXPLAIN the # of rows
for the thread_link join increased from 105 to 16326 so I decided to leave the
category table in. But I agree, it isn't needed.
My other idea I
After Shawn's guidance, I tried inserting with the temporary table method
using this:
CREATE TEMPORARY TABLE tmpStats (KEY(product_id))
SELECT TP.thread_id, COUNT(TP.thread_id) AS num_posts, MAX(TP.post_date) AS
last_update
FROM thread_post AS TP
INNER JOIN thread_link AS TL ON
Hi Guys,
I'm having the same problem with SQL_CALC_FOUND_ROWS
Any query involving SQL_CALC_FOUND_ROWS takes ages!! All my tables are normalized,
indexed, and optimized and the query uses the indices correctly. Many people
responded to my previous thread (when I though it was a fulltext issue
Thanks Brent and Donny, hopefully this info will help get to the root of the problem
with the fulltext search.
The table structure is very, very simple:
mysql describe product_fulltext;
+-++--+-+-+---+
| Field | Type | Null | Key | Default | Extra
I'm gathering by the lack of response that perhaps MySQL is incapable of executing a
count of the number of fulltext matches on 3 million rows.
I really thought that MySQL 4 was really suppose to be able to handle such a load
I still think my configuration may be to blame
?
- John
I'm running into a problem with some queries running on a dedicated mysql server (2.0
GHz, 2GB RAM). Fulltext searching really exemplifies this as most MATCH, AGAINST
queries are taking 5-20 seconds. Performance was excellent for some reason one day
(0.2 - 0.75 seconds) but it was only fast
Have you checked the Optimization section of the manual yet?
http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
Oh yes, as I've attempted to configure the my.cnf file for best performance. The
query is correct. The fulltext index is correct as I built the fulltext index on the
single
Could you send the output of an EXPLAIN for your query?
Sure, pretty sure the index is fine though:
mysql EXPLAIN SELECT COUNT(*) FROM product_fulltext WHERE MATCH (search_text) AGAINST
('black');
Hmmm... looks like it may be impossible to do fulltext searching on 3 million rows as
few have chimed in on this one.
Just to add some background to my setup, I am currently running on MySQL 4.0.17 (plan
to upgrade soon.. will this help??) and I created the index that took 9 minutes to
process
Hi Victor,
The fulltext index was created on 1 column only that is of type text.
The benchmarks I get are very inconsistant...
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('China')
Query took 20.17 seconds
SELECT product_id FROM product_fulltext WHERE MATCH (
Hi Brent,
Oops, sorry about forgetting to post the key buffer size, its:
key_buffer_size 262144000
As far as the combined column I'm using, I did it to make the fulltext as simple as
possible and keep the index at only 1 column. I am aware that I can create an index
for multiple columns but
Thanks for all your help guys,
Using COUNT(*) I get the following:
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('blue
jeans');
+--+
| COUNT(*) |
+--+
|51513 |
+--+
1 row in set (48.58 sec)
The EXPLAIN for the fulltext queries look like
Thanks for the vmstat tip. I ran vmstat 1 on the query on a slightly quicker query so
I wouldn't have a ton of numbers to post from the vmstat.
mysql SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('Pink');
+--+
| COUNT(*) |
+--+
|12231 |
+--+
1
Wow, thanks Brent, nice to learn something new (I'm not much of a 'server' guy)
Tried the iostat and got some results while performing a general query:
mysql SELECT count(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('DISK');
+--+
| count(*) |
+--+
| 4975 |
I am trying to do a fulltext search on my database of 3.3 million rows (~ 1 Gb). I
attempted to make the searching faster by concatenating all columns I wanted to search
and placed them in a table:
product_fulltext (product_id, search_text)
Then I added the fulltext index:
ALTER TABLE
Hi all,
I am working on a search result query in which the user types a keyword and
the top 3 results for each category are displayed with 24 or so total results
on the page. The database is large (8 GB) and the table in which the fulltext
search occurs has 3 million rows.
Tables:
product
20 matches
Mail list logo
