Most Efficient, Stable Version Of MYSQL?
We're still using mysql 3.23.56, so it's time to upgrade! It has never once crashed on us, so we've been content with it so far. However, some of the inefficient query optimizations make us want to upgrade.
Does anyone know what the current most efficient and stable version of MYSQL is? We don't require views or cursors or stored procedures or any of that... Just whatever's the most reliable and best optimized for fast query execution of fairly simple SQL.
I know that 3.23 has some issues with using indices correctly during optimization (most notably in ORDER BY ... DESC). Is that problem fixed in 4?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Is Version 5.0.18 Stable
I was just wondering if mysql version 5.0.18 is a good stable release. We are currently using verion 4.1 and we are happy but as a developer i am looking to upgrade our systems. Please can anyone give me feedback on this.
View Replies !
View Related
Stable Version For Web
I'm looking at various web hosts in the UK. However, the one that provides the best of all my other requirements, only provides version 4.0.22 of MySQL, and tells me they "usually update features such as this when a stable version is known to have been released". From what I've read, 4.1 is a stable version, and provides useful extra features. I know version 5 has been released, but is that classed as "stable"? What version should I reasonably expect? And, er, maybe I should mention my requirements include Windows?
View Replies !
View Related
V5 Stable
I'm migrating my database from FireBird 1.5 to MySQL, for several reasons, being the first SPEED, both in querying and in feeding the beast. But I'm in need of define views in the server, because the queries are becoming too complex to manage in one pass. The DB is gonna get very large (to me at least), now is about 6Gb, but is going to scale to about 30Gb in the next weeks. And the number of users is very small, 3 to 15 max. My question is this: Is version 5 stable enough in regards of Views and Stored Procedures, and MyISAM and InnoDB engines to go for it, or is definitively not worth it to use it for a production support? Yes I know that since it is labelled Alpha it's not officially endorsed to do just that, but if the open bugs have nothing to do with the modelus/functions I mentioned I would take the risk
View Replies !
View Related
Can't Get Stable Copies
I scheduled a cron job for mysqldump to run every day at midnight. But looks like many of the backups are partial, not a complete backup of the origianl db(when I recovered the backups, many of them only got the first table with less rows that there should be). Can anybody tell me where the problem resides? The command I used is: mysqldump --quick --flush-logs --master-data=2 --databases my_database --delete-master-logs --user my_user_name --password=my_password > /path_to_the_dir/my_backup.sql
View Replies !
View Related
Is This An Efficient MySQL Setup?
I would like to offload the MySQL server from my dedicated box in order to speed up page loads. I don't have an additional dedicated server so my only option is to get a VPS. But is offloading the MySQL server to a VPS, albeit a modest one, even worthwhile? I understand this is a very broad question because I am not providing any details but that being the case I am expecting a broad answer
View Replies !
View Related
Rolling Back A DB From Version 5 To Version 4
I've got a local MySQL DB with my MediaWiki data that was created in version 5.0.17, but I recently discovered that my webhost is running version 4.0.27, and when I tried to import the database as-is, I received an error. I'm looking for suggestions as to how I could roll my database back to the older version of MySQL, in case my webhost decides not to let me upgrade. I've considered using an intermediary, such as converting to MDB or CSV, but so far I haven't had much luck with finding a tool to do so without spending a pile o' cash.
View Replies !
View Related
How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post): *table person with fields: -persid: autoincrement id -name: name of the person *table material with fields: -materialid: autoincrement id -material: name of the material eg "wood" *table color with fields: -colorid: autoincrement id -color: name of the color eg "green" *table persmaterial with fields: -persmatid: autoincrement id -persid: link to table person -materialid: link to table material *table perscolor with fields: -perscolorid: autoincrement id -persid: link to table person -colorid: link to table color In the webapplication the search can be entered by the users as a kind of pseudo query: (color=red OR color=blue) AND color=green AND material=iron My question is: how can I automatically transform this pseudo query into an efficient MySQL query? I have tried out some different options: Option 1: (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) Remarks: *I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query? Option 2: SELECT persid FROM person p WHERE (EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid) OR EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid)) AND EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid) AND EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid) Remarks: *very easy to get from pseudo query to MySQL query but what about performance? Option 3: SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING sum(case when pc.colorid in (Ƈ',Ɖ') then 1 else 0 end) >= 1 AND sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1 AND sum(case when pm.materialid=ƈ' then 1 else 0 end)>=1 Remarks: *this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form; Option 4 SELECT DISTINCT pc1.persid FROM perscolor pc1 INNER JOIN perscolor pc2 ON pc1.persid=pc2.persid AND pc2.colorid=2 INNER JOIN persmaterial pm1 ON pc1.persid=pm1.persid AND pm1.materialid=2 LEFT OUTER JOIN perscolor pc3 ON pc1.persid=pc3.persid AND pc3.colorid=1 LEFT OUTER JOIN perscolor pc4 ON pc1.persid=pc4.persid AND pc4.colorid=3 WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL Remarks: *this option requires the pseudo query to be turned into a product of sums form Option 5: SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid Remarks: *very easy to get from pseudo query to MySQL query but what about performance? -- phpMyAdmin SQL Dump -- version 2.6.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 19, 2006 at 01:13 PM -- Server version: 4.1.9 -- PHP Version: 4.3.10 -- -- Database: `aston` -- -- -------------------------------------------------------- -- -- Table structure for table `color` -- CREATE TABLE `color` ( `colorid` int(11) NOT NULL auto_increment, `color` varchar(30) NOT NULL default '', PRIMARY KEY (`colorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `color` -- INSERT INTO `color` VALUES (1, 'red'); INSERT INTO `color` VALUES (2, 'green'); INSERT INTO `color` VALUES (3, 'blue'); INSERT INTO `color` VALUES (4, 'yellow'); -- -------------------------------------------------------- -- -- Table structure for table `material` -- CREATE TABLE `material` ( `materialid` int(11) NOT NULL auto_increment, `material` varchar(30) NOT NULL default '', PRIMARY KEY (`materialid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `material` -- INSERT INTO `material` VALUES (1, 'wood'); INSERT INTO `material` VALUES (2, 'iron'); -- -------------------------------------------------------- -- -- Table structure for table `perscolor` -- CREATE TABLE `perscolor` ( `perscolorid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `colorid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`perscolorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `perscolor` -- INSERT INTO `perscolor` VALUES (1, 1, 1); INSERT INTO `perscolor` VALUES (2, 1, 2); INSERT INTO `perscolor` VALUES (3, 2, 1); INSERT INTO `perscolor` VALUES (5, 3, 3); INSERT INTO `perscolor` VALUES (6, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `persmaterial` -- CREATE TABLE `persmaterial` ( `persmatid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `materialid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`persmatid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `persmaterial` -- INSERT INTO `persmaterial` VALUES (1, 1, 1); INSERT INTO `persmaterial` VALUES (2, 1, 2); INSERT INTO `persmaterial` VALUES (3, 2, 1); INSERT INTO `persmaterial` VALUES (5, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `person` -- CREATE TABLE `person` ( `persid` int(11) NOT NULL auto_increment, `name` varchar(30) NOT NULL default '', PRIMARY KEY (`persid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- Dumping data for table `person` -- INSERT INTO `person` VALUES (1, 'john'); INSERT INTO `person` VALUES (2, 'emily'); INSERT INTO `person` VALUES (3, 'liz');
View Replies !
View Related
What's The Most Efficient Way?
I've done a database scheme and wonder what the most efficient way of storing certain entries would be. The site will have news, reviews and tutorials and these will be under the same categories. So I'm wondering what the best way to do design the database scheme would be. Have one table like I've done now in the "content" table with the posibilty to differentiate the entries with the "post_type" field or have three different tables? Below is the table "content". Field Type Null Default post_id int(10) No post_date datetime No 0000-00-00 00:00:00 post_text text No post_title text No post_cat_id int(4) No 0 post_description text No post_name varchar(200) No post_type int(4) No 0
View Replies !
View Related
How Efficient Is In() , And How Much Is The Most That You Should Put In An In()?
I am just wondering as to the efficiency of the in() function in MySQL. select field1,field2,...,fieldn from table where id in(1,2,3,4,5,6, ... , n); assuming: id is an indexed field. table is a VERY big table (100 000+ - 1 million+ records) what do you think is the largest number of values you could pass to the in() function without completly flattening your server?
View Replies !
View Related
Most Efficient YES/NO...
I) Table_A has fields Data_1, Data_2, Data_3, and Data_4; I need to determine whether value 'X' is present (at least once) in any of the Data_N fields. All I need is a YES/NO answer: YES, 'X' is present (at least once) in one of the Data_N fields of Table_A [Record number(s) not required!], or NO, 'X' is not present in any of the Data_N fields in any of the records in Table_A. II) Similar scenario, but this time Table_A has fields RecID, Data_1, Data_2, Data_3, and Data_4. I need to determine the RecID (none, one, or more) of every record that has value 'X' in any of the Data_N fields. I'm looking for the query that will be fastest and most elegant to implement.
View Replies !
View Related
Quickest And Most Efficient
i run an online game. I want to give each user a ranking, based on how high thier score is.If for example, i had 20,000 players, i dont want to have to update them all one by one, it may strain the server and take a long time. Is there another way i can assign a rank number (rank 1 has the top score and so on)
View Replies !
View Related
Efficient UPDATE
I have a table with the following structure; CREATE TABLE my_table ( id_1 int(11) NOT NULL , id_2 int(10) NOT NULL , stauts tinyint(1) NOT NULL DEFAULT 0 , PRIMARY KEY (id_1) ) Engine =InnoDB'; The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query; UPDATE IGNORE my_table SET id_1 = 74240, id_2 = 5 I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.
View Replies !
View Related
Efficient Tables.
I am starting out on a project where I need to store GPS information. The data consists largely of a series of "Points" each consisting of a longitude,latitude and elevation. On a typical "route" there could be hundreds of points. My question is how can I efficiently store this information. It does not sound sensible to normalise this and add hundreds of rows to a table for each "route". Sample data is along the lines of : - <trkpt lat="54.016942977905273" lon="-1.4903640747070313"> <ele>82.330078125</ele> <time>2006-09-03T07:35:41Z</time> </trkpt> - <trkpt lat="54.016938870772719" lon="-1.490332055836916"> <ele>0</ele> <time>2006-09-03T07:35:42Z</time> </trkpt>
View Replies !
View Related
Mysql-3.23.56-Max And Non-max Version
I recently upgraded my machine to SuSE 8.1 and went about getting all the rpms I could get related to MySQL and install them. I did notice that unlike before, I have a "max" version of 3.23 running and I'm 99.99% sure this was not the case with my previous version (an earlier version of 3.23). What is the difference between the max and non-maxed version... I've not found any info on it
View Replies !
View Related
Mysql 5 Version
I am trying to run Mysql 5 on fedora. I copied the zip files and tried running following command to start the server: shell> scripts/mysql_install_db --user=mysql shell> chown -R root . shell> chown -R mysql data shell> chgrp -R mysql . shell> bin/mysqld_safe --user=mysql & But geting the error: [root@varun mysql5]# Starting mysqld daemon with databases from /data/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 060116 04:31:00 mysqld ended I already have mysql 4 installed and running successfully.. To start with mysql 5, I stopped mysql 4 and executed commands for mysql 5 (as mentioned above). While I was able to run it successfully on my local linux machine but not on the server machine (fedora)
View Replies !
View Related
Old MySQL Version
I'm using the following query to count users in a user table (vocUsers), but only if they're users who aren't represented in a second table (vocProjectOwners). The query works fine on my server, where the MySQL version is 4.1.7. But the production server is still on 3.something, and MySQL there craps out on the "NOT IN" clause. Does anybody know any alternative syntax that might work on that old version of MySQL? Code: $SQL = "SELECT COUNT(*) FROM vocUsers WHERE ID NOT IN (SELECT UserID FROM vocProjectOwners WHERE ProjectID = " . $_REQUEST["id"] . " AND UserID = vocUsers.ID)";
View Replies !
View Related
4.1 Mysql Version
I recently downloaded and installed the newest 4.1 mysql version. I had the 3.x version a few months ago but since then htey have changed the isntall process a little bit. I tried to do the install but whenever I got to the fianl page and tried to exucute the installtion, it would always give me a X and tell me that it couldnt start the windows service. I started the mysql and then used phpmyadmin to log into it. after getting the settigns right, everything was fine except i needed to put a password on my mysql database. I did that thru phpmyadmin and now when i tried to log in it would always tell me tht it couldnt connect and that i should upgrade my mysql verion. i got the same thing when i tried to install phpBB2. I am now downloading the manual istallation of mysql and hoping that will work for me.
View Replies !
View Related
MySQL Version To Use
I have 4.0 currently installed and I'm coding all my scripts in that version... should I keep using it or should I upgrade? If I should upgrade, what version do you recommend, 4.1 or 5.0?I'm confused because I don't make scripts only for me, I code them and put them available to anyone on my website and sometimes I also have some paid jobs to code some websites using mysql databases and the whole point is to which version should I code my scripts?Not every server on the web is running the same mysql version and if I code something that works on 4.1 but not on 4.0 some people might not be able to run the script.Which version do you think that is the most used on the servers? Should I code my scripts to make it compatiable with that version and up?
View Replies !
View Related
PHP Version 5.0.2 MySQL 4.1.7
I am trying to connect from PHP Version 5.0.2 on Windows 2003 Server an IIS to MySQL 4.1.7 but there is an authentication problem. When I run the following PHP script... <?php $connection = mysql_connect("localhost","root","password"); ?> The message I get is... "Client does not support authentication protocol requested by server; consider upgrading MySQL"This seems to be a problem with MySQL 4.1 because I can get it to work perfectly well with MySQL version 4.0.13
View Replies !
View Related
Efficient Storage Of IP Address
I am establishing a database for the purpose of logging access to my secure webserver and am wanting to make the database as efficient as I can because it will be doing a lot of work when the site goes live. What is the most efficient way in a MySQL table to store remote IP addresses? What data type should I use? Should I just go with a basic VARCHAR(15) to allow for 4 sets of 3 digits with 3 decimal separators, or is there a better way?
View Replies !
View Related
Efficient Placement Of Fields
is there any noticable efficiency is ordering the position of the field types?. Like say i place the join keys in a table at the end, and varchars and text at the top of the table does it really matter? Also when i do a query like select * from table where id IN (1), where 1 is usually a primary key int is that quicker than doing where id=1 or is there no difference and is it handling the int as a string or as an int?
View Replies !
View Related
Efficient Database Structure
I have been developing a new website and i need help in deciding the best database structure for it. The site is basically a dating website with various modules like blogs, videos, comments, friends, photos etc. I have created a member table that stores all the basic profile info and created seperate tables for friends, photos, messages, blogs etc and MemberID as foreign key. Now on profile page, i want to display all info related to member like his profile info, his photos, friend list, messages etc and i have to execute 7-10 short queries on profile page for this. Also, i think Joins will not be much helpful as there is one to many relationship e.g there can be more than 1 photo for a member and i am saving each photo in a seperate record. Similar is the case for other tables?
View Replies !
View Related
Creating An Efficient Database
i'm currently writing a web based catalogue system in php using a mysql database. the catalogue has a number of products in it from different brands. i would like to know if it is more efficient to have each brand in a separate table then a "master" table just listing the brand name and corresponding table or all the products in one large table and a "master" table listing each brand in the large table. the large product table would of course have a field to state which brand the product was from. the efficiency would be based on users being able to access the database via html browsers using php and also search the database.
View Replies !
View Related
How To Make Index Most Efficient
I have a large table (> 3,000,000 records). Each records contain a primary key like 'id' and a lots of attributes like 'age', 'department'. I want to build some indices to accelerate my query. I read the document which says that too many indices may slow down the INSERT and UPDATE operations. So is there any rule on how to set indices in such table? If I create an index for every field, would that be a very bad idea? If I create an index on each of two fields but not on their combination, will the indices contribute to queries on the combination?
View Replies !
View Related
Efficient Way To Count Rows
I'm trying to get the number of rows in a table with a very large number of records in it (~9 million). When I run a select count(*) for some criteria (where name='something', etc) it takes around 6-8 secs for the query to return the value. I tried by using SQL_CALC_FOUND_ROWS with a very small LIMIT but then the query was taking even longer. I'm using InnoDB, with query caching enabled. I could look at the information_schema and get the approximate row count but whenever I use a where clause it'll be way off mark.
View Replies !
View Related
Efficient Way Of Mass Indexing?
I have been working on a program that will populate and index a database. The populating doesn't take too long, but the indexing does. My question is: Is there a better approach to indexing this table than the one I'm using right now? I'm doing this through QtSql. headers is a QString array of 48 column names I want to index. for (int i = 0; i < headers.size(); ++i) { q.exec("ALTER TABLE mysqltable ADD INDEX(" + headers[i] + ");"); } example run for 20000 rows: viper,david $ time ./main -r 20000 Database Connection Established 0.520u 0.060s 0:21.27 2.7% 0+0k 0+0io 0pf+0w
View Replies !
View Related
Efficient Way To Left Join?
What is the most efficient way to do a table join, but even if there is no matching foreign key, still return the table on the left. SELECT col, col2, COUNT(col3) FROM tbl1 t1 LEFT JOIN tbl2 t2 ON t1.id = t2.id GROUP BY t1.id But this only works if there is a matching foreign key on t2, I would like all t1 rows to return regardless of whether they exist on t2 or not.
View Replies !
View Related
Difference Between Mysql-3.23.56-Max And Non-max Version
I recently upgraded my machine to SuSE 8.1 and went about getting all the rpms I could get related to MySQL and install them. I did notice that unlike before, I have a "max" version of 3.23 running and I'm 99.99% sure this was not the case with my previous version (an earlier version of 3.23). What is the difference between the max and non-maxed version... I've not found any info on it...
View Replies !
View Related
MySQL Dump Help With Version ;4.0.14-log
Every time I try to do a back up of the databases on our system i get this error: ea990# mysqldump -u root -p --opt -full test > test.sql mysqldump: unrecognized option `--max_allowed_packet=16M' and it doesnt seem to dump anything, I have tried various options, and usually use my db back up script but that doesnt seem to work as well. ---- #!/bin/sh # mysql nightly dump PASS="xxxxx" DATE=`date "+%Y-%m%-%d"` /usr/local/bin/mysqldump --password=$PASS -A > /usr/home/http/mysql_dump/backup.$DATE /usr/bin/bzip2 -9 /usr/home/http/mysql_dump/backup.$DATE ------- but that gives me the same error as above.
View Replies !
View Related
Connecting Php To Mysql Version 5
My problem is,am trying to connect php with mysql so that i can write to the database and also read from the database in de mysql server. I tryed to use the function mysql_connect(),but i get a message like "Call to undefine function mysql_connect()" in the browser. I have mysql and php on de same computer. I think i must make some changes in the php ini file(the configuration settings) but i have no idea how to accomplish it. And also which username must i use in the function since i don't use any username when connecting to mysql or i should leave that argument empty?
View Replies !
View Related
Install 2 Version Of Mysql
I'm a newbie of mysql, i already used mysql 4.0.22nt and it work great And now, I design to install more mysql server on my server of the current version 4.1.7 (reason why: I have some php script that running on 4.0.22nt and I don't to convert them to 4.1.7. Another words, I using it for study purpose only) So, it is possible to install 2 version on the same server like this.
View Replies !
View Related
MySQL 5 Is Latest Version
The lastest full production release of MySQL is version 5. Some hosts do not support version 5 yet but most should be supporting version 4.1. If your version is older than 4.1 please note that in any new threads you start. This is because older versions do not support things like GROUP_CONCAT and subqueries among other things. If you happen to still be on MySQL 3.x then you won't even have the ability to use UNION. If you note that you are using an older version, then when someone is composing a solution to help you, they will give you a version that you can use on your particular release of MySQL. Subqueries can be re-written as joins and in place of a UNION you may create a temporary table and then query results from it for instance.
View Replies !
View Related
MySQL Version 4.1.21-standard Log In Help!
I am using phpmyadmin through my web host provider. I have set up my database and username with a password. I open phpmyadmin, open the SQL query window and try to log in with this mysql -h localhost -u myusername -p When I submit, I get this error: Error SQL query: mysql - h localhost - u myusername - p MySQL said: Documentation #1064 - 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 'mysql -h localhost -u myusername -p' at line 1
View Replies !
View Related
|
TRACKER
