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: http://lists.mys
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'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
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 TP.thread_i
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 ha
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,
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 onl
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
>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');
+--+--+---+-+-+--+--+
>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
singl
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 fo
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 |
+--
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
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
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
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
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
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 product
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
cate
20 matches
Mail list logo
