Fulltext index -first query slow, subsequent queries fast

Hi List I have a table with a fulltext index across five fields, with about 2.2 million records and a data size of about 5.6 GB (index another 3.5 GB). When I test a query that uses fulltext matching, the first run takes about 15-16 seconds to complete. The second run takes about 0.1 sec and

Re: Fulltext index -first query slow, subsequent queries fast

Jerry Schwartz wrote: File system, or disk caching, uses some kind of algorithm to hold chunks of files in system RAM. That way a program can get to it more quickly than if it had to go out to the disk. The algorithms vary, depending upon the smarts of the program and the smarts of the file

Re: Fulltext index -first query slow, subsequent queries fast

mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike snip I have read about sphinx and the good performance boost it provides - unfortunately there is a lot of

Passing db names to a stored proc in MySQL 5

Hello List I have tried dynamically assigning a database name to a stored proc via its parameter list: CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR) BEGIN SELECT * FROM test_db_name.test_table; END; MySQL does not resolve test_db_name to the value

Re: Passing db names to a stored proc in MySQL 5

[EMAIL PROTECTED] wrote: Rory McKinley [EMAIL PROTECTED] wrote on 02/09/2006 07:37:17 AM: Hello List I have tried dynamically assigning a database name to a stored proc via its parameter list: snip snip Use a prepared statement. Build your SQL statement as a string, prepare

Re: Passing db names to a stored proc in MySQL 5

[EMAIL PROTECTED] wrote: snip You were so close! Try it more like this: SET sSQL=CONCAT('SELECT * FROM ', passed_in_db_name, '.sites WHERE site_name=?'); PREPARE query_statement from sSQL snip Doh! I cannot believe that I didn't think of that - seems I was being dense after all! Thanks

Re: [SOLVED]Passing db names to a stored proc in MySQL 5

snip You were so close! Try it more like this: SET sSQL=CONCAT('SELECT * FROM ', passed_in_db_name, '.sites WHERE site_name=?'); PREPARE query_statement from sSQL snip Yup - got it to work - but it is so much hassle that I might seriously reconsider having queries run across databases, as

LEFT JOIN combined with JOIN

Hello List I am running a query to find accounts that not represented in an invoice: Table structure is as follows: Invoice -- invoice_line_number account_number Account_Parameters - account_id parameter_id parameter_value Parameter_Library

Re: Another LOAD Infile Problem

Jason Ferguson wrote: The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). Jason snippety-snip Hi Jason If it's not too late

Re: 1064 error

Schalk Neethling wrote: Greetings What might be causing the 1064 error in the following query? SELECT mem_number, first_name, last_name, area_represented, joining_points + E-Model Challenge + SA Pro Model + Star Model Challenge + Eastern Cape Classic + SA Model Super Star + KZN Model GP +

Re: mysql_safe just ends

and ownership - methinks this is where your problem lies -- Rory McKinley Nebula Solutions +27 21 555 3227 - office +27 21 551 0676 - fax +27 82 857 2391 - mobile www.nebula.co.za This e-mail is intended only for the person to whom it is addressed and may contain confidential

Re: MySQL book

better than the first. Highly recommended. -- Rory McKinley Nebula Solutions +27 21 555 3227 - office +27 21 551 0676 - fax +27 82 857 2391 - mobile www.nebula.co.za This e-mail is intended only for the person to whom it is addressed and may contain confidential information which

Re: Copy the database

is to use mysqldump - this dumps the contents of your database and the data structure as SQL queries, and then you can just treat the dumped file as a batch file when recreating. -- Rory McKinley Nebula Solutions +27 21 555 3227 - office +27 21 551 0676 - fax +27 82 857 2391

Re: Finding the records in one table that are not in another table

Jeff Gannaway wrote: I have 2 tables - ProductsOLD and ProductsNEW. I need to find the records that are in the ProductsOLD table and are NOT in ProductsNEW (this will tell me which products have been discontinued). Here's some sample data: +==+ | ProductsOLD | +==+ +

Re: Secure Database Systems

/PHP. This removes the need for replication to multiple machines, and you can just keep a regular backup copy on a local machine - also reduces some of your security concerns. Regards Rory McKinley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe

Re: Using BETWEEN or = =

= SELECT * FROM federal-married WHERE start = .$_POST['salary']. AND end = .$_POST['salary']; Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)

Re: Convert query from v4 syntax to v3

it's not cockroaches! -- Mom Hi Wash Can you post the error message? Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) -- MySQL General Mailing List For list

Re: Convert query from v4 syntax to v3

On 9 Jan 2004 at 16:48, Odhiambo Washington wrote: * Rory McKinley [EMAIL PROTECTED] [20040109 13:21]: wrote: On 7 Jan 2004 at 11:04, Odhiambo Washington wrote: Hello, I have a query that executes well when run on MySQL-4.x, but not 3.23.x: SELECT popbox.local_part

Re: Convert query from v4 syntax to v3

On 9 Jan 2004 at 15:33, Roger Baklund wrote: * Rory McKinley [...] * Odhiambo Washington I have a query that executes well when run on MySQL-4.x, but not 3.23.x: [...] I can't see what is throwing the syntax error..p'raps I am just being dense. Note that INNER JOIN syntax allows

Re: A little help with this select?

workers currently assigned to projects). If you are just testing, I would suggest that a better bet would be to put dummt entries into the empty table rather than hacking the query. Regards Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world

RE: A little help with this select?

referred to as a 'theta join', and I too would be interested to see how to do this in mySQL. When I've needed such a query, to find 'childless' records, I've always done it with a looped query in the client program. -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent

RE: A little help with this select?

Tyler On Tue, 2004-01-06 at 02:22, Rory McKinley wrote: On 6 Jan 2004 at 9:31, Noamn wrote: Rory wrote: The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries

Re: Query Help

and l.lientraknum like '2003-%' Actually, having written the above workaround, I am puzzled as to why you would need the subquery at all :)...unless of course, above workaround is completely wrong, and I have to eat humble pie again. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL

LEFT JOIN problem

is that the query only returns the first two records and not the third record - contrary to my expectations. I am using MySQL 4.0.15-standard together with PHPMyAdmin 2.5.3. Does anybody know what I am doing wrong? Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds

Re: command line operation problems

Maybe I am stating the obvious but instead of typing mysql -username root -p try : mysql -u root -p HTH Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message

Re: GRANT problem

Hi Jonas Not sure if this will help - in your GRANT statement do you not need to specify a host for the user e.g. GRANT.. to datatal @ your_host_name.?. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand

Re: GRANT problem

because using wildcards for hosts makes me nervous - hey, even paranoid people have enemies :) If anyone knows the answer to this I would be interested in knowing what it is too Sorry I can't be of more help Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people

Re: integer field

Have you tried casting the $myrow element to integer e.g $increase = (int)$myrow['first'] + 1; I think PHP will return the field as text by default and hence the addition will fail.. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world

Re: export to textfile

the MySQL manual, to see them all. HTH. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Bernd Tannenbaum [EMAIL PROTECTED] To: [EMAIL PROTECTED

Re: export to textfile

Hi Bernd Will the query be static? E.g. could you put the SELECT ..INTO OUTFILE query into a batch file and just call that batch file from the command line? mysql -u user -p arb_file.bat Obviously this will be a little more of a problem if you generate the query dynamically each time. Rory

Re: Challenging query....

a, revenue b, revenue c WHERE a.customer_id = 1 AND (YEAR(b.date) = YEAR(a.date) AND MONTH(b.date) = MONTH(a.date) AND b.customer_id = 2) AND (YEAR(c.date) = YEAR(c.date) AND MONTH(c.date) = MONTH(c.date) AND c.customer_id = 3) GROUP BY year, month Regards Rory McKinley Nebula Solutions +27 82 857

Re: Challenging query....

incrementing ASCII numbers and then converting to letters - also makes things way more complicated if you have more than 26 clients :) but still doable. HTH Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who

Re: SELECT problem

a, genre_titles b, genre_titles c WHERE b.genid = 4 AND (b.titleid = c.titleid AND c.genid = 5) AND a.titleid = b.titleid If the number of genres that have to be matched vary, you can always generate your code through a script that loops through and builds the additional parts of the predicate. HTH Rory

Re: LOAD DATA INFILE

. Regards Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Kabbouri Mohammed [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 9

Re: Data from two tables in one query

Hi Jeff Have you looked at UNIONS? An example would be something as follows: SELECT field_1, field_2, field_3 FROM table 1 WHERE blah blah blah UNION SELECT field_1, field_2, field_3 FROM table 1_old WHERE blah blah blah ORDER BY field_1 This should do the trick... Rory McKinley Nebula

Re: newbie select statement question

it is returned from the database (before being input into fixdate()). 3) Within fixdate. See if the results are consistent throughout. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown

Re: Re[2]: [PHP-DB] MySQL Regular expression

don't have 4.0.2 - I would suggest you try strcmp and see ... Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: O Franssen [EMAIL PROTECTED

Re: access denied .... php my admin .....

... Rory McKinley Nebula Solutions 082 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: toby - [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, October 10

Re: Backup, move, restore..?

Hi Taylor To quote from MySQL (2nd ED) by Dubois (pg 725) - InnoDB and BDB tables can be dumped using mysqldump, just like any other kind of tables. So the answer is: nope nothing different. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people

Fw: Re[2]: [PHP-DB] MySQL Regular expression

Apologies for any confusion - wrong list. Should have gone to PHP-DB instead.TGIF. - Original Message - From: Rory McKinley [EMAIL PROTECTED] To: O Franssen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, October 10, 2003 9:14 AM Subject: Re: Re[2]: [PHP-DB] MySQL Regular

Re: MySQL and Php

Hi Caspar Also try phpbuilder.com, and zend.com. Alternatively, try googling PHP tutorials. Regards Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message

Re: Help with a DATETIME Query PLEASE!

... Let me know if I'm warm:) Rory McKinley Nebula Solutions + 27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent

Re: Backup, move, restore..?

. : mysql -u root -p your_destination_db_name your_backup_file_name Then sit back and wait Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From

Re: access denied .... php my admin .....

Hi Toby I am not an expert on permissions within mysql - boy, do I wish I was - but could the problem be that you have only granted permissions to [EMAIL PROTECTED] and not '[EMAIL PROTECTED]' ? Here endeth my knowledge of MySQL permissions Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL

Re: access denied .... php my admin .....

server (when php, apache and mysql are all on the same box) do you connect to 127.0.0.l (loopback address) or do you connect to the IP address of one of the two NICs (e.g. 192.168.y.z)? Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those

Re: MySQL 3.23.58 and sub-seletcs

Hi boka Nope sub-selects to my understanding ar only supported from version 4.1 onwards (still in beta I think) Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original

Re: Easy (?) conditional SELECT

solution I can think of is to have two queries - One gets the max submission number and the other gets the artifacts (using the result of the first query) Not much help I guess..but my 2c. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world