Re: Merging multiple SQL requests
On 2015-02-15 23:55, Learner Study wrote: Hello experts, Is it possible for MySQL server to automatically merge responses for different queries into a single response? Are there any kernel parameters that may dictate that? UNION is used to combine the result from multiple SELECT statements into a single result set. http://dev.mysql.com/doc/refman/5.0/en/union.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
On 2013-06-26 18:31, nixofortune wrote: What would be the best way to convert BIG MyISAM table into InnoDB? We do not have SLAVE. I would do it on another computer. Then copy the table to the server and then add the data that has been added from the original table. And/or i would experiment with TokuDB. I havent had the time to do it myself but will probably soon. I am too looking for a lengthy 1 billion+ row conversion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: space gone after MyISAM REPAIR TABLE
On 2013-06-27 01:27, nixofortune wrote: Now importing with Keys in place. It takes longer, much longer but at least the server is working and customers do not complaint. Schema design is awful, agree. I try to understand the process so will redesign it soon, but any suggestions are welcome. I' not a MySQL super guru so will be glad for hear your sorts, guys. Thanks You could probably reduce your table size a LOT by breaking out keyword and source to their own tables and reference them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Intel Hyperthreading benefits on MySQL 5.5.10+
Should i disable HyperThreading on an Intel Xeon 8-core CPU or leave it on? On older versions of MySQL i read that it should be disabled but with the never versions MySQL is said to handle multiple cores/CPUs better but i cant find anything on HT to be beneficial or not. MySQL 5.5.10+, 24GB DDR 3 RAM, 6 * SSD RAID-10 on Adaptec card, Linux 2.6. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
What MySQL-flavor to choose.
We are about to migrate from MySQL 4.1 to a 5.5 version. We heavily use InnoDB, have an dual quad Nahelem Xeon, 24GB DDR3, 4*SSD in RAID-10 on an Adaptec RAID with 512MB Cache and running under x64 Linux on a modern kernel. We replicate to several other slaves. I only have experience on vanilla MySQL-versions (compile my own). What flavor (MariaDB, MySQL, Percona) should i choose and why? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What MySQL-flavor to choose.
On 2011-02-14 15:31, Singer X.J. Wang wrote: What is your load type? Heavy read but enough write not to benefit much from query cache. It is a webshop app (custom). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What MySQL-flavor to choose.
On 2011-02-14 15:43, Singer X.J. Wang wrote: So I'm assuming OLTP type transaction, then I'm going to recommend MySQL 5.5. Why is that flavor to be chosen over MariaDB with XtraDB or Percona with XtraDB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SQL book recommendation?
[snip] I'm finding the MySQL online manuals hard going in figuring out how to construct SQL queries. Can anyone perhaps recommend a good book that can shed light on the subject? [/snip] http://www.peachpit.com/store/product.aspx?isbn=0321375734 MySQL, Second Edition: Visual QuickStart Guide, 2nd Edition - Larry Ullman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: harmonic mean in SQL
[snip] The mathematical way to add two partial harmonic means to generate new harmonic mean is: Let X1 , X2 be two harmonic means on different rollup rows, generated using n1 and n2 # of facts respectively. The combined harmonic mean would be: (n1 + n2)/( n1/x1 + n2/x2) If you have experience with computing harmonic mean in SQL, please share. [/snip] Unless I am misunderstanding your question the simplest method would be; SELECT ((n1 + n2)/(n1/x1 + n2/x2)) AS Harmonic Mean ...without knowing the structure of the data. If X1 and X2 live on 2 different rows you could write a stored procedure to retrieve the relevant data and produce an output. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Even or Odds numbers
[snip] is there a function, using MySQL 5.0v, that can detect if a numerical value is either an Even or Odd number [/snip] You can use modulus http://www.roseindia.net/sql/mysql-example/mysql-modulus.shtml -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why is MySQL always linked to PHP?
[snip] Jay, PHP is a WEB based Server Side scripting Language. Do not compare it with C. C is a middle -level System programming language. Please stop comparing. [/snip] The statements about C were not mine, it was just the way that the thread was snipped together. Believe me when I say that I know my languages, I have been in this business for 30 years and up until recently even maintained legacy Fortran code. But you are incorrect, PHP is not just a WEB based Server Side scripting language and like most languages it shares, and therefore can be compared to, traits with other languages like C. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How to get last record for each product
[snip] I have a list of product orders in a table with the following structure : OrderID ProductID OrderDate OrderCost What query would I need to get the last order for each productID ? [/snip] MAX(OrderDate) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why is MySQL always linked to Php?
[snip] PHP applications are, for the most part, not that ambitious and mysql is simply the most accessible database with the best developed API. [/snip] I know that you said for the most part and you are absolutely correct. I just want to point out that there are many corporations relying on PHP and MySQL to deliver robust, scalable and enterprise capable applications each and every day. I suppose that is part of the appeal - low barriers to entry with infinite possibilities. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why is MySQL always linked to PHP?
[snip] I merely wished to dispel the common, newb impression that PHP is the only realistic choice. [/snip] I don't think that is the impression but I think that the low barrier to entry and extensive support community make PHP a widely acceptable choice. I have seen many a newb turned off by the communities surrounding other languages (I have seen it in PHP too) but by and large the PHP community is pretty accepting and willing to teach young programmers how to fish. You always have to use the right tool for the job though. The question is are we teaching the inexperienced programmers what the right tools are? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Query Help
[snip] For the life of me I cannot remember how to make a query like this and what it is called. I know it is fairly basic though. Table 1 Product_id Product_Name Table 2 Category_id, Category_name Table 3 Product_id, Category_id Each product can have one or more categories. So I want a result that has Product A one category other category Product B other category [/snip] Can you give us an example of how you would like the output to be? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Creating a Data Dictionary
[snip]
Perhaps I have a conflict of terms here, but my googling mysql data
dictionary turned up material that didn't seem to correspond with my
problem. In python I can create dictionaries:
my_dict = {'1': 'one', '2': 'two'}
Now, I would like to create the equivalent of an enum in which I could
utilize data like that. Of course, I could lump the whole key-value
pairs
into one data and create an enum like that, then parse them later. I'm
just
wondering if there's a more elegant way to do this.
[/snip]
There is an enumerated type
http://dev.mysql.com/doc/refman/5.1/en/enum.html and there are data
dictionaries
http://dev.mysql.com/tech-resources/articles/mysql-datadictionary.html
but neither are really what you want.
Actually what you're describing the reason that we have databases in the
first place - the ability to have data in one column ('1') related to
data in another column ('one') in a record. The most elegant way of
using a database is to use it as it was designed.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: query help
[snip] I have a table similar to this: - |transactions | |ID |DATE |EMPLOYEE| |234 |2010-01-05| 345| |328 |2010-04-05| 344| |239 |2010-01-10| 344| Is there a way to query such a table to give the days of the year that employee 344 did not have a transaction? [/snip] SELECT DATE FROM transactions WHERE EMPLOYEE != '344' GROUP BY DATE; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: substring query
[snip]
I am looking for some guidance on creating a substring query. I have
a column that stores a path to a file. I would like to extract that
file extension and that is it and display it on my results. However,
the paths are different lengths and some extensions are 3 letter and
some are 4, eq 'html'. The only common they all have is the period
before the extension. Anyone created a nested substring query that
can do what I am looking to do?
[/snip]
From the manual -
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_su
bstr
SELECT SUBSTRING('myString', -3)
The result would be 'ing' in this case. Sub your string for myString
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: substring query
[snip]
It may be a little more complicated then I made it out to be.
I am just trying to pull out the file extension but there were some
conditions I did not list.
[/snip]
Thank you for that update, would have been good to have from the start.
SELECT SUBSTRING_INDEX('my.doc','.',-1)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: substring query
[snip] Here is what I came up with. select substring(substring_index(myfile,'.',-2),1,4) AS MyColumn from mydatabase group by MyColumn; That appears to yield what I need. I just need to filter out the results that do not have an extension. [/snip] You can exclude results that do not have a period in them if this is the only period -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Out of range value for column 'datestamp' at row 1
On 2010-05-09 13:29, Prabhat Kumar wrote: INSERT INTO myTable_info (id,range, total_qt, qt_correct, finish_time, username, datestamp) VALUES (NULL,'Kumar', '20', '17', '111', 'Prabhat','* NOW()');* Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax I think problem with* `datestamp` datetime NOT NULL default '-00-00 00:00:00',* Can any one please suggest me, how to deal with this error. Remove the ' around NOW(). ' Makes NOW() a literal string and not a function call. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Pivot Query in
[snip] Date, ProjectCode Building, Number of Copies I want to get a Connsolidate Report of Project Code RD STP 1007304--04---04 (Group by Project Code)(Sumtotal Building wise). I have tried to execute the following code : select pcode, building, sum(ncopies) from request group by pcode,building It is giving the following repott 1007304--RD--04 1007304-STP--05 Here there is a repetition of pcode, How can I avoid this. I want to get the Building Name on top as Column Names and bottom I should get the Count. [/snip] We would need to see some of the raw data to help you but this older article may point you in the right direction with crosstab (pivot) queries; http://www.evolt.org/node/26896 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: better way to backup 50 Gig db?
Gavin Towey wrote: What Shawn said is important. Better options: 1. Use InnoDB, and then you can make a consistent backup with `mysqldump --single-transaction backup.sql` and keep your db server actively responding to requests at the same time. 2. Use something like LVM to create filesytem snapshots which allow you to backup your database, while only keeping a read lock on the db for a second or so. 3. Set up replication and backup the replicated data using any of the above method. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: compare column value to anything in a list of values
[snip]
IN('value1','value2') should work for exact matches, also works for
integer values.
[/snip]
IN will not open and read his CSV file...
[snip]
Is there a simple function or method to compare a value in a column to
one or more items in a comma separated list?
[/snip]
In order to do this you are going to use a programming language or
scripting language. For PHP you could put the values from the CSV list
in an array and the use IN to compare against that array.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
ishaq gbola wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server If you don't specify the absolute location it can be find in DATADIR/DatabaseName/. And after you located the file you have a multitude of choice how to transfer the file. scp,ftp,http,mail all depending on what's installed on the server and what access you got to it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Importing large databases faster
Madison Kelly wrote: Hi all, I've got a fairly large set of databases I'm backing up each Friday. The dump takes about 12.5h to finish, generating a ~172 GB file. When I try to load it though, *after* manually dumping the old databases, it takes 1.5~2 days to load the same databases. I am guessing this is, at least in part, due to indexing. My question is; Given an empty target DB and a dump file generated via: ssh r...@server mysqldump --all-databases -psecret /path/to/backup.sql I use the -e -v -f -q -Q -K parameters for the mysqldump on large tables/databases. It does what you are asking for. Disables the key generation until all of the data is inserted. It also uses multi insert statements and not individual insert statement for every row which speeds up things considerable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to not lock anything?
D. Dante Lorenso wrote: All, I am using MySQL currently, but am starting to think that maybe I don't really need to use an RDBMS. The data I am storing ends up getting indexed with Sphinx because I have full-text indexes for about 40 million records. I have an items table that is heavily updated with 40 million records every 1 or 2 days and I need all those items indexed so they can be searched. The problem that I'm having is that the table is constantly locked because an insert or delete is being performed. I am playing with InnoDB vs MyIsam and have been trying to figure out how to get the best performance. I actually don't care about dirty reads, however, and wouldn't mind if all the 40 mm records could be read/inserted/updated/deleted without any locking at all. Are there known solutions for the kind of storage I am looking for? Anyone have any pointers? Is there a MySQL Storage Engine designed for this kind of usage, or is there a another server that is commonly used along with MySQL for this type of thing? Double buffering : Have two identical tables. Update to the non active and when ready make this table the active. Now do the same updates to the old now nonactive table while the new active table can be read pretty much without disturbance. Make the two tables reside on separate disks if you dont have enough IO. Sure its dirty but it works. If you entirally rebuild your datasets from scratch use this approach : Create an empty table from live table definition (CREATE TABLE tmp SELECT * FROM livetable limit 0;) Now rebuild your dataset to table tmp. Drop live table. Rename tmp table to live table name. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: DELETE DATA FROM TABLE
Krishna Chandra Prajapati wrote: Hi Experts, I have a crm table where 12 millions records inserted/day. We are running report queries on this table and using partitioning features for faster results. we have to maintain 45 days data means 540million records. As per my calculation 540 records will use 1.8 TB of disk space. Total disk space available is 2.3TB. Deleting data doesn't free up the disk space. So, I was thinking of rotating the table. But doesn't have enough disk space. Any Idea, how this task can be performed. Any idea or suggestion is highly appreciated. The space is freed inside the table space but is not seen on disk. Use show table status to show a tables data_free variable. If you prompt want to free the space so you can see it on the file system you can use optimize table command. But the operation can be slow and the table will be locked. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fwd: ODBC MySQL Password as plain text
Tompkins Neil wrote: Following my previous email. I've now configured my database connection using a ODBC DNSLESS SSL connection. However the problem still remains, the password is stored in the ASP file in plain text. Does anyone have any recommendations on how to overcome this issue ? Secure the access to the ASP-source file. You *could* encrypt it but then you have to store the key for it somewhere the ASP can access and . Catch 22. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Alphabetical search to and from
Dave M G wrote: MySQL, This should be a fairly simple question. I have a table with a bunch of people's names. I want to find people who's name begins within a certain range of characters. All names between F and P, for example. What SELECT statement would I use to do that? Thank you for any advice. Slow version (no use of index) : select username from users where left(username,1) between A and B; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Alphabetical search to and from
Or : alter table users add first_f_name char(1) not null; create index first_f_name_idx on users (first_f_name); update users set first_f_name = left(first_name,1); And not the query will use index. select username from users where first_f_name between A and B; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50M records each year, help me choosing the stretegy
sudhir543-nima...@yahoo.com wrote: I have come across a requirement where I need to store a very large amount of data in a table. In one of our app.. we can have around 50 Million records each year.. Can any one guide me in choosing a strategy than can handle this load. 50M records is not that bad if you only store a couple of bytes in every row. So please describe your tables in more detail. And also describe the expected access on the data. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why doesn't mySQL stop a query when the browser tab is closedL
[snip] I just noticed a horrible thing. [/snip] Keep in mind that the query event is server side and is not tied to the browser (client side) once it has begun because of the statelessness of the connection. You would have to have some sort of onClose() event from the browser that would trigger a query cancellation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Virtualizing MySQL
[snip] Virtualization includes overhead. It is fine as long as your application can tolerate that, but if your performance demands grow there will be a point where a DB server in a virtual machine will cause trouble but the same HW as a real machine would still suffice. [/snip] We run MySQL in virtualized environments processing millions of records a day (virtual servers interact with our SAN for storage) and have actually enjoyed performance increases. We are also able to take advantage of advanced disaster recovery/business continuity options available to us in this kind of environment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex conditional statement during select
SELECT this, that, theOther, SUM(IF(SUBSTRING(myDate,1,10) = '20080101' AND SUBSTRING(myDate,1,10) = '20080131'), 1, 0) AS `January` FROM theTable GROUP BY theOther Throws this error... ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')), 1, 0) AS `January` Can I even do something like this during the SELECT. I tried a BETWEEN and while it did not throw errors it did not give back the expected data...I just got 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: List of Publicly Accessible MySQL Databases?
Hi! Check out db4free.net. :) Cheers, Jay Andrew J. Leer wrote: Is there a listing of public MySQL Databases anywhere? Just if someone would be new to databases (not me...other people at my office) and they would want to get a look at an existing working database to learn SQL on? I've found one such database: Genome Bioinformatics db.host=genome-mysql.cse.ucsc.edu db.user=genomep db.password=password But I really don't think the people I'm trying to teach here know much about Genome Bioinformatics (and ah consequently I don't know anything about that either...) Thank you, Andrew J. Leer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DESCRIBE temporary table
I am not finding a quick reference to this, but I wanted to DESCIBE a TEMPORARY TABLE so that I can make sure the index was properly applied. Can this not be done? TIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: TreeView
[snip] how can i create a tree View From a mysql table? [/snip] First you get some leaves. oops, waitnevermind :) I STFW and found http://forums.devarticles.com/mysql-development-50/treeview-of-mysql-tab le-2963.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
Please actually read my reply before asking the same question. As I stated, InnoDB outputs *estimated* row counts in EXPLAIN, whereas MyISAM outputs *accurate* row counts. -jay Krishna Chandra Prajapati wrote: Hi, On myisam storage system mysql explain select ui.user_id, ucp.user_id,ucp.payment_date from user_info ui, user_course_payment ucp where ui.user_id=ucp.user_id; ++-+---++---+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+---+-+-++-+ | 1 | SIMPLE | ucp | index | PRIMARY,user_course_pay_comp1 | user_course_pay_comp1 | 30 | NULL| *256721* | Using index | | 1 | SIMPLE | ui| eq_ref | PRIMARY | PRIMARY | 10 | dip.ucp.user_id | 1 | Using index | ++-+---++---+---+-+-++-+ 2 rows in set (0.00 sec) On innodb storage system mysql explain select ui.user_id, ucp.user_id,ucp.payment_date from user_info ui, user_course_payment ucp where ui.user_id=ucp.user_id; ++-+---++---+-+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+-+-+-++-+ | 1 | SIMPLE | ucp | index | PRIMARY,user_course_pay_comp1 | idx_user_course_payment | 9 | NULL| *256519* | Using index | | 1 | SIMPLE | ui| eq_ref | PRIMARY | PRIMARY | 10 | dip.ucp.user_id | 1 | Using index | ++-+---++---+-+-+-++-+ 2 rows in set (0.00 sec) I have executed ANALYZE TABLE COMMAND on both the system (innodb and myisam) Yet there is a small difference. Highlighted in red color Is it the behavior of myisam or innodb or interal working of the storage engines. Thanks, Krishna On Wed, Apr 2, 2008 at 9:06 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Wed, Apr 2, 2008 at 5:06 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Horribly ugly stuff I know I sure as heck am not going to spend half an hour to turn those queries into something understandable, and I expect no one else will either. If you want help please remove all extraneous details (turn table and columns names in t1,t2,col1,col2, etc or descriptive names like parent, child, datetime_end) and send out something that is easy to reproduce. You get a cupcake if you include ddl that populates itself with random data. Also, using /G instead of a semi colon will make database output a heck of a lot easier to read in email form. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb vs myisam
The MyISAM isn't scanning more rows. It's that the InnoDB rows output in EXPLAIN is an estimate and the MyISAM one is accurate... -jay Krishna Chandra Prajapati wrote: Hi All, I have same table configuration, every thing same except the storage engine. Explain result on innodb system mysql explain select ucpr.course_amount, ucpr.coupon_amount, ucp.payment_order_id, ui.course_id, uct.ref, uet.ref, ui.user_id, ucpr.coupon, ucp.payment_service_id, ucp.payment_id FROM user_course_pricing ucpr, user_info ui, course c, user_course_payment ucp left outer join user_cc_trans uct on ucp.payment_order_id=uct.payment_order_id left outer join user_ec_trans uet on ucp.payment_order_id=uet.payment_order_id WHERE ucp.payment_order_id is not null and date_format(ucp.payment_date, '%m-%Y')='05-2007' and ucp.user_id = ucpr.user_id and ucp.user_id = ui.user_id and ui.course_id = c.course_id and ucp.payment_id in (1, 2, 5, 6) and ui.course_id not in (1005, 1007, 1008) and ui.course_id not in (select course_id from course_attribute where ATTRIBUTE ='HOSTED_AFFILIATE') ORDER BY ucp.Payment_date; +++--+-+---++-+--+---+---+ | id | select_type| table| type| possible_keys | key| key_len | ref | rows | Extra | +++--+-+---++-+--+---+---+ | 1 | PRIMARY| c| range | PRIMARY | PRIMARY| 10 | NULL | 134 | Using where; Using index; Using temporary; Using filesort | | 1 | PRIMARY| ui | ref | PRIMARY,idx_user_info_2 | idx_user_info_2| 10 | dip.c.course_id | 279 | Using index | | 1 | PRIMARY| ucp | eq_ref | PRIMARY,user_course_pay_comp1 | PRIMARY| 10 | dip.ui.user_id | 1 | Using where | | 1 | PRIMARY| ucpr | eq_ref | PRIMARY | PRIMARY| 10 | dip.ucp.user_id | 1 | Using where | | 1 | PRIMARY| uct | ref | user_cc_trans_order_id| user_cc_trans_order_id | 10 | dip.ucp.payment_order_id | 1 | | | 1 | PRIMARY| uet | index | NULL | idx_user_ec_trans | 35 | NULL | 13959 | Using index | | 2 | DEPENDENT SUBQUERY | course_attribute | unique_subquery | PRIMARY | PRIMARY| 44 | func,const | 1 | Using index; Using where | +++--+-+---++-+--+---+---+ 7 rows in set (0.00 sec) Explain result on myisam system mysql explain - select ucpr.course_amount, ucpr.coupon_amount, ucp.payment_order_id, ui.course_id, uct.ref, uet.ref, ui.user_id, ucpr.coupon, - ucp.payment_service_id, ucp.payment_id - FROM user_course_pricing ucpr, user_info ui, course c, user_course_payment ucp left outer join user_cc_trans uct on - ucp.payment_order_id=uct.payment_order_id left outer join user_ec_trans uet on ucp.payment_order_id=uet.payment_order_id - WHERE ucp.payment_order_id is not null and date_format(ucp.payment_date, '%m-%Y')='05-2007' and ucp.user_id = ucpr.user_id - and ucp.user_id = ui.user_id and ui.course_id = c.course_id and ucp.payment_id in (1, 2, 5, 6) and ui.course_id not in - (1005, 1007, 1008) and ui.course_id not in (select course_id from course_attribute where ATTRIBUTE ='HOSTED_AFFILIATE') - ORDER BY ucp.Payment_date; +++--+-+---++-+--++--+ | id | select_type| table| type| possible_keys | key| key_len | ref | rows | Extra
RE: Im being dumb!
[snip] -Original Message- From: roger.maynard [mailto:[EMAIL PROTECTED] Sent: Thursday, March 06, 2008 7:33 AM To: mysql@lists.mysql.com Subject: Im being dumb! I got 4 tables: Table A | ID | Description1 | Table B | ID | Description2 | Table C | ID | Description3 | Table D | ID | Description4 | ALL Ids ARE COMMON Values and NONE are MISSING How can I create | ID | Description 1 | Description 2 | Description 3 | Description 4 | SELECT a.ID,a.Description1,b.Description2,c.Description3,d.Description4 FROM TableA a INNER JOIN TableB b ON a.id = b.id INNER JOIN TableC b ON a.id = c.id INNER JOIN TableD b ON a.id = d.id Doesn't give me the result What am I doing wrong? Can I do this? [/snip] Try this SELECT a.ID, a.Description1, b.Description2, c.Description3, d.Description4 FROM TableA a LEFT OUTER JOIN TableB b ON a.ID = b.ID LEFT OUTER JOIN TableC c ON a.ID = c.ID LEFT OUTER JOIN TableD d ON a.ID = d.ID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with timestamp and leap seconds?
[snip]
I had a bit of BFOTO and tried simple inserts.
mysql create table t (f timestamp);
Query OK, 0 rows affected (0.00 sec)
mysql insert into t values ('2008-03-04 16:17:00');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+-+
| f |
+-+
| 2008-03-04 16:17:37 |
+-+
1 row in set (0.00 sec)
[/snip]
The column type needs to be DATETIME.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with timestamp and leap seconds?
[snip] The column type needs to be DATETIME. Thank you for pointing me at TIMESTAMP versus DATETIME. I'll read http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.html thoroughly when I can. Can you give a little more detail as to why DATETIME is necessary? [/snip] It was much too quick a reply on my part but it is my understanding that a TIMESTAMP field is updated according to server time and you cannot actually insert a value. I may be wrong as I have never tested this. On the other hand a DATETIME field accepts inserts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large Database Performance - Reference Sites?
You will likely need to be a lot more specific about what you are asking for here, David. What is a large select? What constitutes a large update? What number of joined tables composes a multi join in your specific case? What is text functionality? -jay David Stoller wrote: Can Someone with Large Databases (100million records 20K-row avg )X5 contact me for some questions, regarding performance on: 1. Text functionality 2. Performance large selects multi joins large updates bulk inserts Best Regards, [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] R D DBA Malha Technology Park Jerusalem 91481, Israel 972-2-6499241 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of heterogeneous joins
Nope, no difference, AFAIK. Alex K wrote: Any ideas pertaining this newbie question? Thank you so much, Hi Guys, Is there a performance hit when joining across multiple databases as opposed to joining multiples tables in one database? Suppose the same tables are available across all databases. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: executing query from the command line -- need help
[snip] I'm new to mysql. I would like to issue a query from the command line and pass the result to an update done on the command line within the same script. See below. My question is how can I run a select from the command line and pass the values to an update SELECT SYS_ID, SYS_LOCATION, SYS_IP FROM PROD_SERVER; UPDATE TEST_SERVER SET SYS_ID = value passed from above SYS_LOCATION = value passed from above SYS_IPADDRESS = value passed from above; [/snip] Start here http://dev.mysql.com/doc/refman/5.1/en/declare-local-variables.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: executing query from the command line -- need help
[snip] SELECT SYS_ID, SYS_LOCATION, SYS_IP FROM PROD_SERVER; UPDATE TEST_SERVER SET SYS_ID = value passed from above SYS_LOCATION = value passed from above SYS_IPADDRESS = value passed from above; [/snip] And here http://dev.mysql.com/doc/refman/5.1/en/user-variables.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: executing query from the command line -- need help
[snip] Hello Jay. Thanks for your reply but where is your solution to my problem. I'm lost here. Help me -- please Thx -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23, 2008 11:29 AM To: Brown, Charles; mysql@lists.mysql.com Subject: RE: executing query from the command line -- need help [snip] SELECT SYS_ID, SYS_LOCATION, SYS_IP FROM PROD_SERVER; UPDATE TEST_SERVER SET SYS_ID = value passed from above SYS_LOCATION = value passed from above SYS_IPADDRESS = value passed from above; [/snip] And here http://dev.mysql.com/doc/refman/5.1/en/user-variables.html [/snip] Always reply to all so that this goes back to the list. I found this in my junk folder. When you retrieve the value from the first query assign that value to a variable which can then be used in the second query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sun and mysql
[snip] I am still amazed by the fact that youtube is worth 1.5 billion and MySQL AB barely 1 billion. Did they sell under price? Or does Google just have way to much many to spend/waste? [/snip] Or that Facebook is 'worth' multiple billions when they do not really have a way to make money yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prepared SQL statements - Faster performance?
Are you using the PREPARE STATEMENT server-side syntax or an emulated prepared statement like in PDO? -jay mos wrote: I would like to speed up my Select queries since I'm executing approx 5,000 of them, same syntax but the search values for 2 columns will change with each query. Will I see any performance increase if I prepare the statement and use parameters? (I don't need to use the query cache since the result set will be returned only once for each set of search values.) TIA Mike MySQL 5.024 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Forbidden subquery
No problem. I hope by now you figured out I made a typo... :) The WHERE in the DELETE should be prod_price_chg_flag='O', not =X :) -jay Jerry Schwartz wrote: Hi Jerry! The very last sentence on: http://dev.mysql.com/doc/refman/5.0/en/delete.html is Currently, you cannot delete from a table and select from the same table in a subquery. [JS] Yes, I knew that. I just thought that illegal query was the best way of expressing what I wanted to do. But, to bypass that, you can create a temp table and join to that: [JS] Bingo! It didn't occur to me to make a temporary table. That should do exactly what I want! Thanks. CREATE TEMPORARY TABLE to_delete SELECT prod_price.prod_id FROM prod_price WHERE prod_price.prod_price_chg_flag = X; DELETE prod_price FROM prod_price JOIN to_delete ON prod_price.prod_id=to_delete.prod_id WHERE prod_price.prod_price_chg_flag = 'X'; DROP TABLE to_delete; Cheers, Jay Jerry Schwartz wrote: What I want to accomplish is expressed best as DELETE FROM prod_price WHERE prod_price.prod_price_chg_flag = O AND prod_price.prod_id IN (SELECT prod_price.prod_id FROM prod_price WHERE prod_price.prod_price_chg_flag = X) ; This is clear, concise, and completely illegal. I want to delete every O record which has an accompanying X record. I tried using a self-join like this DELETE FROM p1 USING prod_price AS p1 JOIN prod_price AS p2 ON p1.prod_id = p2.prod_id WHERE p1.prod_price_chg_flag = O AND p2.prod_price_chg_flag = X ; And got a storage engine error 134 (MyISAM table). I'm not even certain that this would have done what I wanted, but I guess I won't find out. Here's what the table prod_price looks like: Table: prod_price Create Table: CREATE TABLE `prod_price` ( `prod_price_id` varchar(15) NOT NULL default '', `prod_id` varchar(15) default NULL, `prod_price_del_format` varchar(255) default NULL, `prod_price_val_date` date default NULL, `prod_price_chg_flag` char(1) default NULL, `prod_price_disp_curr` varchar(10) default NULL, `prod_price_disp_price` decimal(10,2) default NULL, `prod_price_end_curr` varchar(10) default NULL, `prod_price_end_price` decimal(10,2) default NULL, `prod_price_reg_price` varchar(5) default NULL, `prod_price_changed` tinyint(1) default NULL, `prod_price_added` datetime default NULL, `prod_price_updated` datetime default NULL, PRIMARY KEY (`prod_price_id`), KEY `prod_id` (`prod_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I'm running 5.0.45-community-nt. Suggestions? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another cry for help..
You could use a view: CREATE VIEW all_scores SELECT s.tid, s.vid, s.uid, s.highScore FROM score s JOIN objects o ON s.tid = o.tid JOIN itemtypes it ON s.vid = it.vid JOIN users u ON s.uid = u.uid WHERE o.shortname = %s /* Should these ANDs really be ORs? */ AND i.itemtype LIKE %s; SELECT highScore:= @my_high_score FROM all_scores WHERE u.username = %s LIMIT 1; SELECT COUNT(*):= @total_scores FROM all_scores; SELECT COUNT(*):= @total_greater_my_score FROM all_scores WHERE highScore @my_high_score; SELECT ((@total_great_my_score + 1) / @total_scores) * 100 AS percentile; Hope this helps, Jay Anders Norrbring wrote: Brent Baisley skrev: You might be able to use variables to store the result of the query. Although I've never tried assigning the result of a query to a variable, only field values. SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s) ) * 100 AS percentile WHERE s1.tid = @tid AND s1.vid = @vid AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) Brent At a first glance, it doesn't work at all, I get NULL results from it, but I haven't spent any time trying to locate the problem yet.. So, I'm still open for ideas! On 12/20/07, Anders Norrbring [EMAIL PROTECTED] wrote: Hi.. I'm struggling with a query that I'm trying to simplify as much as possible, but I can't seem to get rid of using the very same subqueries several times. Would there be a way to optimize the following so I get rid of subqueries that do the exact same thing more than once? SELECT (count(*)+1) / (SELECT COUNT(*) FROM score AS s2 WHERE s2.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s2.vid = (SELECT vid FROM itemtypes WHERE itemtype LIKE %s)) * 100 AS percentile FROM score AS s1 WHERE s1.tid = (SELECT tid FROM objects WHERE shortname = %s) AND s1.vid = (SELECT vid FROM types WHERE itemtype LIKE %s) AND s1.highScore (SELECT highScore FROM score WHERE tid = s1.tid AND vid = s1.vid AND uid = (SELECT uid FROM users WHERE username = %s)) -- 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: Forbidden subquery
Hi Jerry! The very last sentence on: http://dev.mysql.com/doc/refman/5.0/en/delete.html is Currently, you cannot delete from a table and select from the same table in a subquery. But, to bypass that, you can create a temp table and join to that: CREATE TEMPORARY TABLE to_delete SELECT prod_price.prod_id FROM prod_price WHERE prod_price.prod_price_chg_flag = X; DELETE prod_price FROM prod_price JOIN to_delete ON prod_price.prod_id=to_delete.prod_id WHERE prod_price.prod_price_chg_flag = 'X'; DROP TABLE to_delete; Cheers, Jay Jerry Schwartz wrote: What I want to accomplish is expressed best as DELETE FROM prod_price WHERE prod_price.prod_price_chg_flag = O AND prod_price.prod_id IN (SELECT prod_price.prod_id FROM prod_price WHERE prod_price.prod_price_chg_flag = X) ; This is clear, concise, and completely illegal. I want to delete every O record which has an accompanying X record. I tried using a self-join like this DELETE FROM p1 USING prod_price AS p1 JOIN prod_price AS p2 ON p1.prod_id = p2.prod_id WHERE p1.prod_price_chg_flag = O AND p2.prod_price_chg_flag = X ; And got a storage engine error 134 (MyISAM table). I'm not even certain that this would have done what I wanted, but I guess I won't find out. Here's what the table prod_price looks like: Table: prod_price Create Table: CREATE TABLE `prod_price` ( `prod_price_id` varchar(15) NOT NULL default '', `prod_id` varchar(15) default NULL, `prod_price_del_format` varchar(255) default NULL, `prod_price_val_date` date default NULL, `prod_price_chg_flag` char(1) default NULL, `prod_price_disp_curr` varchar(10) default NULL, `prod_price_disp_price` decimal(10,2) default NULL, `prod_price_end_curr` varchar(10) default NULL, `prod_price_end_price` decimal(10,2) default NULL, `prod_price_reg_price` varchar(5) default NULL, `prod_price_changed` tinyint(1) default NULL, `prod_price_added` datetime default NULL, `prod_price_updated` datetime default NULL, PRIMARY KEY (`prod_price_id`), KEY `prod_id` (`prod_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I'm running 5.0.45-community-nt. Suggestions? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update but insert if not exist
INSERT ... ON DUPLICATE KEY UPDATE: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Cheers, Jay J Trahair wrote: This is a question I want to know the answer to, as well! Is there any way of avoiding looking up a specific record in a table to see if it exists, before deciding whether to INSERT INTO or UPDATE, eg: mstrSQL = SELECT * FROM Shops WHERE ShopReference = ' grd1.TextMatrix(numRowNo, 1) ' Set rsRecordset = New ADODB.Recordset gconn.CursorLocation = adUseServer rsRecordset.Open mstrSQL, gconn, adOpenDynamic, adLockOptimistic If rsRecordset.EOF = True Then mstrSQL = INSERT INTO Shops (ShopNameInFull, ShopReference, TillNumber) VALUES (strShopNameInFull, strShopReference, strTillNumber) mconn.Execute mstrSQL Else mstrSQL = UPDATE Shops SET ShopNameInFull = 'strShopNameInFull', ShopReference = 'strShopReference', TillNumber = 'strTillNumber' WHERE ShopReference = ' grd1.TextMatrix(numRowNo, 1) ' mconn.Execute mstrSQL End If just thought I'd ask! Jonathan Trahair -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spfile in Mysql......
Sujatha S wrote: Mysql should bring this as there new feature in there next release! Unlikely. Dynamic changes are, well, dynamic. Permanent stuff goes in the my.cnf. -jay Regards, Sujatha On Nov 27, 2007 11:44 AM, Shanmugam, Dhandapani [EMAIL PROTECTED] wrote: Hello, The dynamic changes made on mysql server instance gets vanished once the instance goes down...!! Is there any way for mysql to store the dynamic changes on my.cnf file automatically ..?(like Oracle) , so on next startup mysql automatically pickup the dynamic changes made from my.cnf file Unfortunately there is not. You should alter your my.cnf file to record the changes you make. -- MySQL General Mailing List For list archives: *http://lists.mysql.com/mysql*http://lists.mysql.com/mysql To unsubscribe: * http://lists.mysql.com/[EMAIL PROTECTED]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: Giant database vs unlimited databases
[snip] The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. [/snip] Not true and it has been proven time and again by the likes of Yahoo and others that size. We routinely use MySQL for large data stores (upwards of half a billion records in a single table) and with proper management we have performance equal to or better than the above mentioned products without the overhead required by either of those. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Subquery
Indeed, as you say, Brent, correlated subqueries are not well-optimized in MySQL. The specific subquery (the IN() subquery) demonstrated in the original post is, however, optimized in MySQL 6.0 :) More comments inline. Brent Baisley wrote: You are using a correlated subquery, which MySQL is terrible at. Whenever you find yourself doing a correlated subquery, see if you can switch it to a derived table with a join, which MySQL is far better at. A derived table is like a virtual table you create on the fly. It's very simple, just assign a name to your query and then treat it as if it is a regular table. Actually, in this case, no need for a derived table. A simple join will suffice: SELECT * FROM projects p JOIN project_tags pt ON p.project_id = pt.project_id JOIN tags t ON pt.tag_id = t.tag_id WHERE tags.name='foo'; Make sure you've got indexes on p (project_id), pt (project_id, tag_id), t (name) Cheers, Jay So your query would look something like this: SELECT projects.* FROM projects JOIN (SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id) AS ptagids ON project.id=ptagids.project_id Your IN has become a JOIN and mysql optimizes it far better. On Oct 19, 2007, at 6:57 PM, Ryan Bates wrote: I'm trying to determine why a subquery is slower than running two separate queries. I have a simple many-to-many association using 3 tables: projects, tags and projects_tags. Here's the query I'm using to find the projects with a given tag: SELECT * FROM projects WHERE id IN (SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id); (0.36 sec) Compare that with splitting it into two queries: SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id (0.00 sec) /* returns 1, 2, 3 */ SELECT * FROM projects WHERE id IN (1, 2, 3); (0.00 sec) Why is it so much faster? Looking at the explain statement (below) of the one with the subquery, it appears it's not using the primary key index on the projects table. Why is it that MySQL doesn't perform this simple optimization? And is there a solution that will allow me to still use a subquery? I realize I can use a join instead of a subquery, but this is a simplified example. Here's the explain statement: *** 1. row *** id: 1 select_type: PRIMARY table: projects type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 15433 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: tags type: ref possible_keys: PRIMARY,index_tags_on_name key: index_tags_on_name key_len: 258 ref: const rows: 1 Extra: Using where; Using index *** 3. row *** id: 2 select_type: DEPENDENT SUBQUERY table: projects_tags type: ref possible_keys: tag_id key: tag_id key_len: 5 ref: my_database.tags.id rows: 10 Extra: Using where Here's the table dumps: CREATE TABLE `projects` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `tags` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `index_tags_on_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `projects_tags` ( `project_id` int(11) default NULL, `tag_id` int(11) default NULL, KEY `tag_id` (`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I'm using MySQL 5.0.37. Thanks in advance. Ryan -- 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]
The value of NULL in Uniqued Columns
This is more of a philosophical issue than anything, but it has jumped up to bite us so I thought I'd make others aware; Since NULL has no value they can be entered multiply times into unique columns. Some will say that NULL is a value and therefore should be unique in this case (only one NULL allowed) and others will say that since NULL has no intrinsic value it can be entered into a unique column as many times as you would like. We have found this behavior in multiple database types (MS-SQL, Oracle) so it is not unique to MySQL, it is just where we noticed it. It is not one of those things that we thought aboutuntil now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER BY but disregard stop words
[snip] Is there any way to use ORDER BY in such a way as to have it ignore words such as the, a, an, and the like? [/snip] I haven't tested this but you might be able to do it with a little REGEX and a HAVING clause; SELECT REGEX(words) AS undesirable FROM table HAVING stuff undesirable -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a natural sort order for version numbers and release code names
Daevid Vincent wrote: I'm trying to get some 'release/version numbers' to sort properly. mysql SELECT ReleaseID, Name FROM releases ORDER BY Name DESC; +---+-+ | ReleaseID | Name| +---+-+ |18 | Unspecified | |20 | Next Patch | |58 | LOCset | |74 | Abashiri| |54 | 4.6.0 (Folsom) | -- 4.5.10 should be here |99 | 4.5.9 | |98 | 4.5.6 | |93 | 4.5.5 (Purdy) | |97 | 4.5.4 | |96 | 4.5.3 | |94 | 4.5.2 | | 100 | 4.5.10 | -- should be ^ there |91 | 4.5.1 Deferred | |78 | 4.5.1 (Leavenworth) | |95 | 4.2.7.4 | |92 | 4.2.7.3 | |90 | 4.2.7.2 | |87 | 4.2.7.1 | |88 | 4.2.7.0 | I like this order, especially with the top four, except for that 4.5.10 should be higher up, just under 4.6.0, not under 4.5.2 as it is now. So I tried the + 0 trick which makes things even worse (notice the 4.2.6.1 and 4.2.6.0 -- yipes!): mysql SELECT ReleaseID, Name FROM releases ORDER BY Name + 0 DESC; What about ORDER BY REPLACE(Name, '.', '') + 0 DESC? +---+-+ | ReleaseID | Name| +---+-+ (18,20,58,74) are moved :( |54 | 4.6.0 (Folsom) | |78 | 4.5.1 (Leavenworth) | | 100 | 4.5.10 | |91 | 4.5.1 Deferred | |93 | 4.5.5 (Purdy) | |94 | 4.5.2 | |96 | 4.5.3 | |97 | 4.5.4 | |98 | 4.5.6 | |99 | 4.5.9 | |82 | 4.2.6.1 |? |76 | 4.2.2 | |75 | 4.2.4 | |72 | 4.2.1 | |73 | 4.2.3 | |67 | 4.2.6.0 |? I'm pretty sure this is going to involve some sort of splitting the version from the release codeword via some string functions, and then operating on that part. D.Vin http://daevid.com --- eval() is my favorite templating engine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query not returning Data
[snip] SELECT * FROM Sight_Hearing_Help WHERE 'type_help' = Eye Exam Glasses AND 'board_action_date' BETWEEN 07-01-2007 AND 12-31-2007 LIMIT 0 , 60; [/snip] Try WHERE type_help LIKE '%Eye Exam Glasses%' and look at your dates in the database themselves even if they are varchars, they are likely formatted -MM-DD. Remove the limit first to make sure you are returning data or make it more like LIMIT 60 first. SELECT * FROM Sight_Hearing_Help WHERE type_help LIKE '%Eye Exam Glasses' AND board_action_date BETWEEN '2007-07-01' AND '2007-12-31' LIMIT 0 , 60; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2008 conference fee?
Sid Lane wrote: stupid non-technical ?: does anyone know what the registration fee is going to be for the 2008 conference? my mgr needs a # today to put in next yr's budget I couldn't find it on the conference site. if it's not been finalized could someone tell me what it was last year? Hi! I believe the conference fees will be similar to last year: $1,095 conference w/o tutorials $495 tutorials $1,495 conference w/tutorials Plus, as always, there are significant discounts available for a variety of groups (students, government, user groups, educators, etc..) I *think* that's right... :) Cheers, and post back here if you've got any further ?s. Jay Pipes Program Chair, MySQL Conference and Expo 2008 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
funky characters in columns
I did some googleing and some other searching, now I am looking for a
cure all. I have a column into which it appears that a carriage return
has been inserted and it is mucking about with some queries;
mysql select dealerLong from profile where id = '130';
++
| dealerLong |
++
|.9040
++
(the number contained therein should be 98.9040). I know that the column
should be set up as a float, but this is an older database and was not
set up that waymine left to correct.
For troubleshooting purposes, once I had narrowed down the problem
column I did the following
mysql select concat('|', dealerLong, '|') from profile where id =
'130';
+--+
| concat('|', dealerLong, '|') |
+--+
| |
+--+
You will note the way that the column displays, appearing to have no
data at all. This is typically caused by having a carriage return
somewhere in the column.
update profile set dealerLong = replace(dealerLong, char(13), ) where
id = '130';
has no affect. So I need to see all of the characters inn the column so
that I can determine how to replace.
Can someone point me in the correct direction? I sure do appreciate any
help that you can give me. I certainly do not want to have to go through
each record that is borked up separately.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: funky characters in columns
[snip] Try: replace(replace(dealerLong, '\n', ''), '\r', '') [/snip] Didn't work, perhaps because they are hidden. I ended up taking the long road; update table set foo = replace(HEX(foo), '0D', ''); update table set foo = UNHEX(foo); HEX allowed me to see the carriage return (0D) and then use replace syntax to fix. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: csv to mysql
[snip] : i,m looking for a solution for my PDA that Doesn't have a DB Solution installed on it : so im having to write to CSV Files for my Forms , i'm needing a way that when i sink my PDA with my wireless network it Moves the Entire CSV File into a MySQL database :any Suggestions :? [/snip] LOAD DATA INFILE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql] duplicating lines
Hi Craig, would you mind posting the SHOW CREATE TABLE for the tables in question? I'm having trouble determining what is the primary key for your service ticket table... Thanks, -Jay Weston, Craig (OFT) wrote: Hello again, I am having a duplication of results problem. I believe my query (below) is giving me exactly what I deserve in writing it. What it returns to me is a row for each status. What I would most want would be a single row with the oldest status - IE the status datetime that happened earliest. What I am trying to do is determine when a service desk ticket first enters any one of these three categories. I am not using distinct on `thedata2`.`Source` as this does not effect the result set. I have 2 tables. One of the tables lists all the ticket information at time of the ticket being closed. The other has an entry referenced by ticket number for each time a ticket is touched or updated. So what I am trying to do is identify the last time it was touched with the appropriate status change. Does anyone have any idea what I could do to eliminate the duplicate with the oldest time? I am experimenting in the idea of a subquery but can't think of anything else. ??? Thanks, craig SELECT `thedata2`.`Source`, `thedata1`.`Status`, `thedata2`.`Priority`, `thedata1`.`start_Time`, `thedata1`.`Close_Time`, `thedata1`.`workday`'cycletime' FROM `thedata2` Inner Join `thedata1` ON `thedata1`.`Source` = `thedata2`.`SourceSR` WHERE (`thedata1`.`Status` like 'Resolved' OR `thedata1`.`Status` like 'Restored' OR `thedata1`.`Status` like 'Isolation') and `thedata2`.`Open_Time` BETWEEN '2007-02-01 00:00' AND '2007-08-31 23:59:59' And ((`thedata2`.`Priority` = 1 and `thedata1`.`workday` 14400) OR (`thedata2`.`Priority` = 2 and `thedata1`.`workday` 86400) or (`thedata2`.`Priority` = 2 and `thedata1`.`workday` 172800)) This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does this MySQL client exist?
Christoph Boget wrote: I did a search and couldn't find anything like what I'm looking for and though I doubt something like this does exist, I figured I'd ask anyway. Is there a client (not phpMyAdmin) that can connect to a server (that is running MySQL) using SSH and connect to the database that way? Right now, the only way we are allowed to access the actual server is by using either SSH or SFTP. The only way we can access the MySQL database on that server is either use phpMyAdmin (which I don't particularly care for; not to disparage the hard work of the developers, it's just a matter of personal preference) or use the command line. I'm hoping that there is client software out there that can do what I'm looking for. Does it exist? Use the mysql client, like so: # ssh [EMAIL PROTECTED] [EMAIL PROTECTED] ~ mysql --user=dbuser --password somedatabasename Enter password: XXX mysql SELECT blah blah... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Simple questio SQL
[snip] I have a Table and want to know the most visited products. Products - Id - Name - Visited [/snip] SELECT Id, Name, count(Visited) AS Total_Visits FROM product GROUP BY(Id) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding count of spaces in a string
[EMAIL PROTECTED] wrote: We have numerous identical tables with a varchar column that holds data like this: 0 0 0 1 0 1 0 25 7 0 139 0 9. Essentially there are a bunch of integers with a single space as a separator. There _should_ be no more than 30 entries ( and 29 spaces ), but sometimes the system misfires and there are more or less. Is there a MySQL solution to getting a count of the spaces present in the field, figuring that spaces + 1 will equal entries? It's fairly straight forward using a PHP application, but I'd like to get the DB server to accomplish this task. Not having much luck finding a solution in the manual. SELECT CHAR_LENGTH(field_name) - CHAR_LENGTH(REPLACE(field_name, ' ', '')) as num_spaces FROM my_table; Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: thread_concurrency in linux
Andrew Braithwaite wrote: Does anyone know if thread_concurrency works in linux or is it just limited to Solaris and Windows? Hi! That variable only affects Solaris, as the Solaris threading library supports thr_setconcurrency(). innodb_thread_concurrency, however, can affect all platforms, AFAIK: http://www.mysql.org/doc/refman/5.0/en/innodb-parameters.html#option_mysqld_innodb_thread_concurrency I know the general rule is number of CPU's*2 but will this actually have any effect with Linux's threading model? Nope, at least AFAIK. -jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage - MyISAM vs InnoDB
Hi! Comments inline. Edoardo Serra wrote: SELECT sum(usercost) FROM cdr WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59' If I run it on the MyISAM table, MySQL choose the right index (the one on the calldate column) and the query is fast enough If I run it on the InnoDB table, MySQL uses no index even if an EXPLAIN query tells me that 'calldate' is between the available indexes Here are my EXPLAIN results mysql EXPLAIN SELECT sum(usercost) FROM cdr_innodb WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+--+-+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+-+--+-+--+-+-+ | 1 | SIMPLE | cdr | ALL | calldate,date-context-cause | NULL | NULL| NULL | 5016758 | Using where | ++-+---+--+-+--+-+--+-+-+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT sum(usercost) FROM cdr_myisam WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+---+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+-+--+-+--++-+ | 1 | SIMPLE | cdr | range | calldate,date-context-cause | calldate | 8 | NULL | 772050 | Using where | ++-+---+---+-+--+-+--++-+ 1 row in set (0.11 sec) Another strange thing is that the EXPLAIN on InnoDB says the table has 5016758 rows but a SELECT count(*) returns 4999347 rows (which is the correct number) The rows returned in EXPLAIN SELECT (and SHOW TABLE STATUS) for InnoDB tables is an estimate. For MyISAM, it is the actual number of rows in the table. This is because InnoDB has to track a version for each row in the table (for transactional isolation), and MyISAM does not, which makes it much easier to just have a simple row count for the table. This estimate of rows returned is what is used by the optimizer to determine what execution plan is optimal for this particular query. In this case, there are approximately 772K out of 5M rows which meet the WHERE condition -- or about 15% of the total number of rows in the table. There is a certain threshold, where above it the optimizer will choose to do a sequential table scan of the data, versus do many random seeks into memory or disk. It seems that you are hovering around the threshold for where the optimizer chooses to do a sequential table scan (InnoDB) vs a range operation on a btree with lookups into the data file for each matched row in the index (MyISAM). The difference in returning an estimate vs. the actual row count *might* be the cause of the difference in execution plans. Or, it could have something to do with the weights that the optimizer chooses to place on bookmark lookups in MyISAM vs a quick table scan in InnoDB. I'd be interested to see what the difference in *performance* is? Also, in *either* engine, if you are executing this particular query a *lot*, the best thing for you to do would be to put the index on (calldate, usercost) so that you have a covering index available to complete the query. Cheers! Jay Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: servers full potential / FT searches locking tables
SELECTs don't lock the table. Are you having frequent UPDATEs while selecting? That would be the reason for locks. -jay Justin wrote: Ok.. Straight to the point.. Here is what I currently have. MySQL Ver 14.12 Distrib 5.0.27 RHEL vs 5 584GB Raid 5 storage 8GB of RAM and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon) what my question is.. is am I utilizing the servers potential with the following as my settings. The server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,388,600 key cache age threshold 300 key cache block size 1,024 key cache division limit 100 language /usr/share/mysql/english/ large files support ON large page size 0 large pages OFF lc time names en_US license GPL local infile ON locked in memory OFF log OFF log bin OFF log bin trust function creators OFF log error /var/log/mysql/error.log log queries not using indexes OFF log slave updates OFF log slow queries OFF log warnings 1 long query time 10 low priority updates OFF lower case file system OFF lower case table names 0 max allowed packet 1,073,740,800 max binlog cache size 4,294,967,295 max binlog size 1,073,741,824 max connect errors 10 max connections 5,000 max delayed threads 20 max error count 64 max heap table size 16,777,216 max insert delayed threads 20 max join size 18446744073709551615 max length for sort data 1,024 max prepared stmt count 16,382 max relay log size 0 max seeks for key 4,294,967,295 max sort length 1,024 max sp recursion depth 0 max tmp tables 32 max user
Re: servers full potential / FT searches locking tables
A read lock does not prevent other reads. Rolando Edwards wrote: SELECTs do lock the tables implicitly. According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), here is what the first bulletpoint says under the heading A lock on data can be acquired implicitly or explicitly: For a client that does nothing special to acquires locks, the MySQL server implicitly acquires locks as necessary to process the client's statments sdafely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement. - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, August 27, 2007 2:26:29 PM (GMT-0500) America/New_York Subject: Re: servers full potential / FT searches locking tables SELECTs don't lock the table. Are you having frequent UPDATEs while selecting? That would be the reason for locks. -jay Justin wrote: Ok.. Straight to the point.. Here is what I currently have. MySQL Ver 14.12 Distrib 5.0.27 RHEL vs 5 584GB Raid 5 storage 8GB of RAM and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon) what my question is.. is am I utilizing the servers potential with the following as my settings. The server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,388,600 key cache age threshold 300 key cache block size 1,024
Re: user permissions to all DB
solidzh wrote: 2007/8/21, Jay Pipes [EMAIL PROTECTED]: Terry wrote: Hello, I want to grant a user all permissions to all DBs on the system as well as any new DBs that show up. I want to avoid having to modify permissions everytime a new DB is added. Is there a way to do this? GRANT SELECT, INSERT, CREATE, ... ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password'; That's well but why not, grant all on *.* to 'user'@'host' identified by 'pwd'; ? Because then you give the user SUPER, FILE, ALTER, SHUTDOWN, and PROCESS privileges, which probably isn't a good idea... :) Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user permissions to all DB
Terry wrote: Hello, I want to grant a user all permissions to all DBs on the system as well as any new DBs that show up. I want to avoid having to modify permissions everytime a new DB is added. Is there a way to do this? GRANT SELECT, INSERT, CREATE, ... ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password'; Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user permissions to all DB
Yep. Terry wrote: Just to verify, will that include all new databases? On 8/20/07, Jay Pipes [EMAIL PROTECTED] wrote: Terry wrote: Hello, I want to grant a user all permissions to all DBs on the system as well as any new DBs that show up. I want to avoid having to modify permissions everytime a new DB is added. Is there a way to do this? GRANT SELECT, INSERT, CREATE, ... ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password'; Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user permissions to all DB
Terry,
I absolutely agree with Rolando on this.
Rolando,
Although I agree with you, I was only trying to answer Terry's question :)
Cheers,
Jay
Rolando Edwards wrote:
You must be very careful when granting permissions on every database this way.
Here is why:
By giving a user permissions on all databases this way,
you also give away permissions to the 'mysql' schema.
This is where the grant tables live.
A person could
1) insert new users into mysql.user like this
INSERT INTO mysql.user VALUES (...);
2) delete users from mysql.user like this
DELETE FROM mysql.user WHERE host='...' AND user='...';
3) maliciously or accidently change passwords like this
UPDATE mysql.user SET PASSWORD=PASSWORD('insert new password') WHERE
host='...' AND user='...';
4) grants additional privileges to himself like this
UPDATE mysql.user SET grant_priv='Y',execute_priv='Y',... WHERE host='...' AND
user='...';
After setting those privilges, the person would then run FLUSH PRIVILEGES;
Then, all the privileges the user gave himself would go into effect !!!
Of course, the user would need the RELOAD privilege to do FLUSH PRIVILEGES;
Even if the user does not have RELOAD privilege, the user could still give
himself this privilege in a delayed way like this
UPDATE mysql.user SET reload_priv='Y' WHERE host='...' AND user='...';
Then the next time mysqld is restarted, all the privileges the user gave
himself would go into effect !!!
It is therefore NOT RECOMMENDED the user be granted privileges over the 'mysql'
schema.
Instead to this:
GRANT SELECT, INSERT, CREATE, ... ON db1.* TO 'username'@'hostname' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, CREATE, ... ON db2.* TO 'username'@'hostname' IDENTIFIED
BY 'password';
GRANT SELECT, INSERT, CREATE, ... ON db3.* TO 'username'@'hostname' IDENTIFIED
BY 'password';
GRANT SELECT, INSERT, CREATE, ... ON db4.* TO 'username'@'hostname' IDENTIFIED
BY 'password';
Grant the necessary privileges to each database individually and leave out
'mysql'.
Unfortunately, you cannot grant privileges to all databases and revoke
privileges from one schema ('mysql' in this instance)
You must enumerate the databases you specifically want to grant the user
privileges to.
GUARD THE mysql SCHEMA WITH YOUR LIFE, PLEASE
- Original Message -
From: Jay Pipes [EMAIL PROTECTED]
To: Terry [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Monday, August 20, 2007 3:41:52 PM (GMT-0500) America/New_York
Subject: Re: user permissions to all DB
Terry wrote:
Hello,
I want to grant a user all permissions to all DBs on the system as
well as any new DBs that show up. I want to avoid having to modify
permissions everytime a new DB is added. Is there a way to do this?
GRANT SELECT, INSERT, CREATE, ... ON *.* TO 'username'@'hostname'
IDENTIFIED BY 'password';
Cheers,
Jay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert Select query problem
Ed Reed wrote:
Hi All,
I have an issue that I need to resolve that is difficult to explain. I
hope that someone can understand what I*m trying to do and shed some
light on a solution. Here goes.
I have three tables, inventory, which is a list of transactions with
positive and negative values; request, which essentially is a temporary
table that gets deleted after it*s used here; and purchase, which holds
the solution as to whether an item is to be purchased or removed from
inventory,
CREATE TABLE `inventory` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
CREATE TABLE `purchase` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Source` int(11) DEFAULT NULL,
`Item` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`Required` varchar(100) NOT NULL DEFAULT '',
`Qty` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
My Inventory and Request tables have data in them like this,
Insert Into `inventory` (Item, Qty)
Values
('Apples',5),
('Bananas',4),
('Cherries',6),
('Apples',-1),
('Bananas',1),
('Cherries',-2),
('Apples',3),
('Bananas',-7),
('Cherries',19),
('Apples',-5),
('Bananas',88),
('Cherries',6);
Insert Into `request` (Required, Qty)
Values
('Apples', 12),
('Bananas', 112),
('Cherries', 5);
Now what I*d like to do is create a single Insert Select query that
creates a record in my purchase table for each of the items in my
request table based on the number of items available in my inventory.
But, if there aren't enough items in the inventory to cover the amount
requested, I need to have a second record for that item in the purchase
table with the qty difference to another source. So based on the data in
the inventory my current totals are,
+--+--+
| Item | Sum(Qty) |
+--+--+
| Apples | 2|
| Bananas | 86 |
| Cherries | 29 |
+--+--+
and based on the qty of items in my request I would like to have a
purchase table that looks like this,
++--+-+
| Source | Item | Qty |
++--+-+
| 1 | Apples | 2 |
| 0 | Apples | 10 |
| 1 | Bananas | 86 |
| 0 | Bananas | 26 |
| 1 | Cherries | 5 |
++--+-+
with a source of 1 meaning pull the items from inventory and a source
of 0 means purchase them from somewhere else.
Can anyone help me with this?
Try this:
INSERT INTO purchase (Source, Item, Qty)
SELECT
1, totals.Item, r.Qty
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty = totals.TotQty
UNION ALL
SELECT
0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
SELECT Item, SUM(Qty) AS TotQty
FROM inventory
GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty totals.TotQty;
cheers,
Jay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2007 MySQL Community Survey - Got Ten Minutes to Spare?
[EMAIL PROTECTED] wrote: Problems again with the survey design and functionality. Page 4 asks questions about Falcon, and to be honest I don't know anything about Falcon, but you've required answers to advance and only offered yes' and no' as choices. Without a Don't know choice as an option, your results will be totally skewed. I designed survey questionnaires in my past life as a psychologist, and this one is fatally flawed. I'm done. I won't try again. Well, since I'm not a psychologist, I did the best I could, David. I'll remember your input for the next one and hopefully do a better job. Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2007 MySQL Community Survey - Got Ten Minutes to Spare?
Thanks for the input! Hopefully, I've fixed the problem. Please retry. It should have saved your previous answers. Thanks much! Jay J.R. Bullington wrote: I received the same as David. The question was: What OS do you currently use? Please check all that apply: (page 3, question 4 or 5): I chose Linux 2.6.x and Windows. It kept telling me that I needed to answer the question. I changed the answer to just Linux and it let me thru. Just to provide a little more info for you. J.R. From: [EMAIL PROTECTED] Sent: Tuesday, August 07, 2007 9:35 AM To: 'Jay Pipes' [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: 2007 MySQL Community Survey - Got Ten Minutes to Spare? Having a moment of altruism, I started doing the survey only to find that it wouldn't let me advance to the next page (from either page 1 or page 2, can't recall). I kept getting an error of an answer is required of this question even when I had provided one. No good deed goes unpunished perhaps... David So I was gonna take this survey (I don't need or care about the book, just wanted to help you out) and honestly, it's more like a quiz -- needless to say I didn't do it. :-| -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Friday, August 03, 2007 2:33 PM To: mysql@lists.mysql.com Subject: 2007 MySQL Community Survey - Got Ten Minutes to Spare? 2007 MySQL Community Survey - Bribe Included I've created a survey on SurveyMonkey that I am hoping to get a bunch of responses for. The survey will help the community team identify how (in)effectively we communicate development and other goals and also what features you, our community users, most want in future versions of MySQL. So, hey, give us ten minutes of your time and help us make MySQL better. A Blatant Bribe for Participating And for those who need a bribe, we'll be giving away two Apress books (each) to 3 random survey takers. The survey is anonymous, but if you would like to go into the drawing for the books, just include your email address in the very last question...otherwise, just leave it blank. Link to the 2007 Community survey: http://www.surveymonkey.com/s.aspx?sm=ONCdTFPxjyq55NAUBUJ_2fvQ_3d_3d Thanks much! Jay Pipes Community Relations Manager, North America MySQL, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2007 MySQL Community Survey - Got Ten Minutes to Spare?
2007 MySQL Community Survey - Bribe Included I've created a survey on SurveyMonkey that I am hoping to get a bunch of responses for. The survey will help the community team identify how (in)effectively we communicate development and other goals and also what features you, our community users, most want in future versions of MySQL. So, hey, give us ten minutes of your time and help us make MySQL better. A Blatant Bribe for Participating And for those who need a bribe, we'll be giving away two Apress books (each) to 3 random survey takers. The survey is anonymous, but if you would like to go into the drawing for the books, just include your email address in the very last question...otherwise, just leave it blank. Link to the 2007 Community survey: http://www.surveymonkey.com/s.aspx?sm=ONCdTFPxjyq55NAUBUJ_2fvQ_3d_3d Thanks much! Jay Pipes Community Relations Manager, North America MySQL, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Camp II - August 23-24 - Brooklyn, New York
=== MySQL Camp II - August 23-24 - Brooklyn, New York == The second MySQL Camp is happening August 23rd and 24th at Polytechnic University in Brooklyn, New York. Like the first MySQL Camp, this one is a *completely free*, *community-driven* event. About MySQL Camp The camp is a relaxed, barcamp-style unconference that gets MySQL and FLOSS community members, users, and developers together for the purpose of driving innovation and participation. Sessions at the un-conference are proposed and voted on by the campers both onsite and before the camp begins. The camp is part hackfest, part interactive learning and sharing, and part relaxed networking event. The focus of MySQL Camp II is participation. Come prepared to shout out ideas, challenge traditional thinking, make new friends, and work with fellow community members on both your own and community projects. Limited Registration Registration for MySQL Camp II is restricted to only 200 participants, and space is filling up quickly. To register, email Jay Pipes ([EMAIL PROTECTED]) the following information: - Your Name - Your Company or Affiliation (if applicable) - Your Location - Your Email Address Links and More Information -- Up to date information about the camp is available at http://mysqlcamp.org. Information about Polytechnic University is available at http://www.poly.edu. For hotel information and room sharing, please check the MySQLCamp.org website continually as the camp dates approach. == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: selecting everyting from 2 non-identical tables.
[snip] I have two non-identical tables. They are pretty similar except a few fields. I want to select everything from both for example table1 id name age table2 id name height I want id name height age even if it returns null values. select * from table1, table2 seems to give repeat rows for some reason. [/snip] Use a left outer join, assuming that 'name' is the same in both; SELECT t1.id, t1.name, t1.age, t2.height FROM table1 t1 LEFT OUTER JOIN table2 t2 ON(t1.name = t2.name) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorting by a list of possible results in a column....
[snip] I have a query that selects a list of results, ordering them by the status field. However, I want to further sort that by the type of status, that is: Undefined Ready for Review Top Priority Priority Completed Etc... Every sort that I try, of course, sorts alphabetically. Is there a way to define how the sort function works in the order by? [/snip] You can specify ORDER BY foo DESC or ASC and you can do multiple ORDER BY's SELECT * FROM table ORDER BY foo, bar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stored procedure not working in legacy ASP
[snip] The stored procedure is in MySQL, but when called using ASP it fails to return more than the first record. Anyone? [/snip] You need a while loop. Does the SP work from the command line properly? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [X-POST] Fastest way to dump this huge table
[snip] I have a huge MySQL table, 2.1 million records, 200MB. Once a week I need to dump it in CSV format and zip the file. This is not on my server, and it's in production, so I don't want to risk testing different methods and possibly hanging up their server for a period of time, so I wanted to seek advice here first to find what's the best way to proceed. I can easily use PHP to query the table for the results I want and write a file line by line and then zip it, but I'm worried that might take too long and hang up the machine. The other way to go is some kind of sql dump command, which I guess would be faster, but not sure how much control I'd have over the exact format of the file. Any suggestions which way I should proceed? Not hanging up their server is my prime concern. [/snip] SELECT * INTO OUTFILE /directory/myfile.csv FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' FROM table; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: secure port 3306
[snip] I have a client that needs to be able to remotely connect to port 3306 securely. I have tried to suggest an SSH Tunnel, but they do not want their clients to have SSH access. Another problem is that even if we do tunnel, it needs to go thru one server that is connected to the Internet and into the MySQL server which is NOT accessible from the Internet. Any suggestions? [/snip] IPSec tunnel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize code?
Hi Jerry, comments inline Jerry Schwartz wrote: I need (ultimately) to update some prices in a prod_price table. First, I need to locate a product and its associated prices using a field prod.prod_price_prod_id which is not unique, and is often null, but it is indexed. (Subsequent operations will use PRIMARY keys, but I haven't gotten there yet.) I further qualify a product by an associated pub.pub_code, to weed out possible duplicate prod_pub_prod_id entries from different publisher. Good... I would move to lookups/joins on a primary key ASAP for performance. My SELECT statement is SELECT SQL_CALC_FOUND_ROWS prod.prod_num, prod_price.prod_price_end_curr, prod_price.prod_price_end_price, prod_price.prod_price_disp_curr, prod_price.prod_price_disp_price FROM pub JOIN prod JOIN prod_price WHERE pub.pub_id = prod.pub_id AND pub.pub_code IN (ener,fit,govt,heal,id,life,manu) AND prod.prod_id = prod_price.prod_id AND prod.prod_pub_prod_id = 101771 AND prod_price.prod_price_disp_curr = 'USD' AND prod_price.prod_price_end_curr = 'USD'; An EXPLAIN of this query looks pretty good: *** 1. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: PRIMARY,pub_id,prod_pub_prod_id key: prod_pub_prod_id key_len: 766 Whoooaaahhh is it really a 766-byte-wide key? That's going to kill you. ref: const rows: 2 Extra: Using where *** 2. row *** id: 1 select_type: SIMPLE table: pub type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 45 Same here. 45-byte-wide PK is a killer. ref: giiexpr_db.prod.pub_id rows: 1 Extra: Using where *** 3. row *** id: 1 select_type: SIMPLE table: prod_price type: ref possible_keys: prod_id key: prod_id key_len: 46 Same ref: giiexpr_db.prod.prod_id rows: 2 Extra: Using where*** 1. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: PRIMARY,pub_id,prod_pub_prod_id key: prod_pub_prod_id key_len: 766 Same ref: const rows: 2 Extra: Using where *** 2. row *** id: 1 select_type: SIMPLE table: pub type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 45 ref: giiexpr_db.prod.pub_id rows: 1 Extra: Using where *** 3. row *** id: 1 select_type: SIMPLE table: prod_price type: ref possible_keys: prod_id key: prod_id key_len: 46 ref: giiexpr_db.prod.prod_id rows: 2 Extra: Using where As you can see, if first retrieves the (possibly multiple) prod records based upon the prod_pub_prod_id, which is keyed. Then it hops over to the pub table using the common pub_id field, which is the PRIMARY key in the pub table, so it can check my IN condition. Finally, it picks up (possibly multiple) prod_price records using the common field prod_id. The optimization seems pretty good. A single execution of this query, using the CI MySQL, is reported to take .05 seconds. Unfortunately, I have about 20,000 products to process; so at a minimum I would expect it to take 1,000 seconds. Even ignoring the overhead from PHP, this is going to run for awhile. Does anyone have any suggestions for improving my code? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on millions of records in one table?
He, Ming Xin PSE NKG wrote: Hi, Pipes Is it reliable to use MySQL 5.1 in a commercial product now since it is still a beta version? Hmmm. Probably depends on what you are doing with it... But, in general, it's fairly sta ble at this point but, like all beta software, cannot be considered a production version. Cheers, Jay -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 18, 2007 10:04 PM To: Brent Baisley Cc: He, Ming Xin PSE NKG; mysql@lists.mysql.com Subject: Re: Problem on millions of records in one table? Brent Baisley wrote: It all depends on how complicated your data and searches are. I've got tables that add 2-3 million per day and I don't have performance problems. Although we only retain at most 500 millions records, not a full years worth. That said, you can get horrible performance out of mysql with tables as small as 100,000 records if you don't structure your queries correctly or use a good table structure. If I know the tables are going to grow quickly and I don't need the entire dataset all the time, I'll use merge tables. This makes it easy to remove old data easily from the default table set. Hi! Have you tried out the new partitioning features of MySQL 5.1 to do this? Would be cool if you had some performance numbers comparing the older MERGE table method with the newer partitioning... Cheers! Jay - Original Message - From: He, Ming Xin PSE NKG [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 17, 2007 11:03 PM Subject: Problem on millions of records in one table? Hi,all The number of the records in one table increase constantly. As evaluated, the amount would increase to at least 30 millions within one year. So we worry about whether mysql could handle such a big amount of records with good performance. Or need we some other solutions to avoid this problem ,such as using Partition, dividing a big table and etc. Any help or idea would be greatly appreciated. Best Regards mingxin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help please: SELECT in binlog?
Fionn Behrens wrote: We recently switched to mysql5 and while we were at it we also changed our logs from text to bin as suggested by the migration script we had (probably created by debian people). Now I unfortunately had to reconstruct what had happened during a faulty run of our application and I could not get any SELECT statement from the log!? The usual search engine run didnt bring up anything useful, so my questions are: 1) Are the selects somwhere in the binlogs and I just have not found the right voodoo to make the come out? No, no selects. Only commands that change data are replicated, AFAIK. 2) If they are not there by default, can I configure mysqld to store SELECTs in a binlog? Not that I know of. 3) If not, is the old text log all I can go back to? You can have both, AFAIK. The general query log keeps all queries, including SELECTs. Binlog only has data-modifying queries. Cheers, jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on millions of records in one table?
Brent Baisley wrote: It all depends on how complicated your data and searches are. I've got tables that add 2-3 million per day and I don't have performance problems. Although we only retain at most 500 millions records, not a full years worth. That said, you can get horrible performance out of mysql with tables as small as 100,000 records if you don't structure your queries correctly or use a good table structure. If I know the tables are going to grow quickly and I don't need the entire dataset all the time, I'll use merge tables. This makes it easy to remove old data easily from the default table set. Hi! Have you tried out the new partitioning features of MySQL 5.1 to do this? Would be cool if you had some performance numbers comparing the older MERGE table method with the newer partitioning... Cheers! Jay - Original Message - From: He, Ming Xin PSE NKG [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 17, 2007 11:03 PM Subject: Problem on millions of records in one table? Hi,all The number of the records in one table increase constantly. As evaluated, the amount would increase to at least 30 millions within one year. So we worry about whether mysql could handle such a big amount of records with good performance. Or need we some other solutions to avoid this problem ,such as using Partition, dividing a big table and etc. Any help or idea would be greatly appreciated. Best Regards mingxin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to tell if something hasn't happened yet
[snip] select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); [/snip] This is close, but it does not exclude previous days. I only want to see those that have not logged in today. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to tell if something hasn't happened yet - SOLVED
[snip] [snip] select s.* from store s where s.id not in (select t.storeid from trans t where t.created=date(now())); [/snip] This is close, but it does not exclude previous days. I only want to see those that have not logged in today. [/snip] select store.storeid, store.stname from store where store.storeid not in ( select transaction.storeid from transaction where substring(transaction.created, 1, 10) date_sub(current_date(), interval 1 day) ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to tell if something hasn't happened yet
Good day gurus and gurettes! I have a table; | transactionid | int(11) | NO | PRI | | auto_increment | | username | varchar(32) | NO | | || | storeid | varchar(6) | NO | | || | action| int(4) | NO | | || | code | int(2) | NO | | || | ipAddr| varchar(32) | NO | | || | created | datetime| NO | MUL | || | created_by| varchar(32) | NO | | || I used to have a query (I have misplaced it somehow) where I could tell which storied had not logged in (created) today yet. No matter how hard I try I cannot remember the query. What I need is a query that will tell me at any given point during the day which storeid is not online (created). I do have a sister table where all of the storeid's are, so the join happens there. I can test created for IS NULL but it does not limit the query to today. select store.storeid, store.stName from store left outer join transaction on(store.storeid = transaction.storeid) where transaction.created IS NULL and store.active = 'yes' group by store.storeid; How can I limit this to today only without having to hard code a date into the query? TVMIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is the Falcon license listed as 'PROPRIETARY' in 5.2.3?
Baron Schwartz wrote: Greetings, On 5.2.3: select plugin_name, plugin_license from plugins; +-++ | plugin_name | plugin_license | +-++ | binlog | GPL| | partition | GPL| | ARCHIVE | GPL| | BLACKHOLE | GPL| | CSV | GPL| | Falcon | PROPRIETARY| | FEDERATED | GPL| | MEMORY | GPL| | InnoDB | GPL| | MyISAM | GPL| | MRG_MYISAM | GPL| | ndbcluster | GPL| +-++ Why is Falcon listed as PROPRIETARY? I assume that won't be the eventual license when it's finished. Is it just work in-progress to make it GPL or something? Hi! This was an oversight, and due to the original Netfrastructure code from Jim Starkey. It is now fixed in the codebase, as evidenced here: http://lists.mysql.com/commits/24222 Cheers! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Inserting a file in MySQL
[snip]
How do i insert a file in a blob field from the command line ?
[/snip]
From http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
mysql UPDATE t
SET blob_col=LOAD_FILE('/tmp/picture')
WHERE id=1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do NULL values slow down the database?
Ales Zoulek wrote: Hi, I've read reacently, that it's not good to use columns with NULL values in MySQL, because it slows down the select queries over that columns. Is it true? Or do that affects only some situations or some versions? Are there some relevant statistics about that? There is not really a noticeable slowdown just for having NULLable columns. However, there are situations where separating off frequently accessed columns from (often NULLable) infrequently accessed columns into two or more tables can provide very good performance improvement, as the infrequently accessed columns have much less likelihood from taking up space in memory, especially in memory-starved applications. Cheers, Jay On 3/29/07, Reinhart Viane [EMAIL PROTECTED] wrote: Hello list, I have a table events in a database that has a field named duration. This field is a mediumint containing an amount of minutes (eg 65, 87, 10368) Now I need these to be outputted into a h:mm (so 65 will be represented as 1:05) My complete query is: select YEAR(events.workdate) as theyear, (sum(events.duration)/60),clients.name, persons.name from events, persons, clients where events.personid= persons.personid and events.clientid= clients.clientid group by clients.name, events.personid, theyear; this does, off course not give me the wanted result. How can I convert these numerical entries to hh:mm in my query? (days do not matter, I just need hours and minutes, thx) Regards and thanks, Reinhart Viane D-studio Graaf van Egmontstraat 15/3 2800 Mechelen [EMAIL PROTECTED] +32(0)15 44 89 01 -- 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: improving performance of server
Could you post the actual code you are using for the INSERT? Also, what storage engine are you using? Jay andrew collier wrote: hello, i am having some trouble getting mysql to perform decently on my machine. it is a 2 GHz dual core AMD 64 machine (although i am presently running a 32 bit linux system) with 1 Gb RAM and 1 Gb swap partition. the data i am using is loaded into a table described by: CREATE TABLE IF NOT EXISTS strikes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, epoch DATETIME, usecMEDIUMINT UNSIGNED, fdate DOUBLE, lat FLOAT(6,4), lon FLOAT(7,4), error TINYINT UNSIGNED, nstat TINYINT UNSIGNED ); the data itself is pretty big: 70082053 records. during the loading process mysqlq pretty much hogs the CPU but uses only around 5% of the RAM. it takes 13m50s to load the data. there is a lot of disk activity, but this is just reading the data and there is virtually no swap space in use. adding the following index: INDEX coords (lat,lon) takes a really long time. once again the hard disk is working hard, but there is no swapping, so obviously this is just due to database reads. CPU usage is about the same. in the end after 60 hours i gave up: had to reboot to windows to do some other stuff. but it was just taking unreasonably long anyway. i am pretty sure that a lot more of this processing could be done without that much disk activity and i am guessing that is what is slowing the whole process down. the configuration i have in my.cnf is: [client] port= 3306 socket = /var/run/mysql/mysql.sock [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 32M max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 128K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout please could someone give me an idea of how i might go about making this whole thing a little more efficient? thanks! best regards, andrew collier. -- Need cash? Click to get a payday loan http://tags.bluebottle.com/fc/CAaCMPJktTdJ7Iu6tODsIDX46jYJlhKA/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Increasing the Query Cache Size has performance ?
Not sure what is going on, but the version of MySQL you are using is ancient. The current version of MySQL is 5.0.37. Even MySQL 4.1 (which has been end-of-lifed) has a latest version of 4.1.22. If it is a bug you are seeing, it likely has been fixed in a later version. Cheers, Jay Kishore Jalleda wrote: Hello Everybody, I increased the query_cache_size on one of our main servers from 100 MB to 250 MB, since I was seeing a very high rate lot of Qcache Low Mem prunes. The server was fine for 15 minutes and the Low mem prunes went down to almost zero, but then started getting too many connections errors and the queries were taking too long to execute, and only after the roll backed the change the server started behaving normally. This could not be a co-incidence as the server has been running fine for months even under heavy traffic conditions. Has anybody ever experienced such a thing or know what could be the cause .. Server info 4.1.11-Debian_4sarge3-log 32GB RAM Max_connections : 400 Thanks Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
