Search Results

Search found 27396 results on 1096 pages for 'mysql query'.

Page 60/1096 | < Previous Page | 56 57 58 59 60 61 62 63 64 65 66 67  | Next Page >

• Slow MySQL query....only sometimes

  - by Shane N

  I have a query that's used in a reporting system of ours that sometimes runs quicker than a second, and other times takes 1 to 10 minutes to run. Here's the entry from the slow query log: # Query_time: 543 Lock_time: 0 Rows_sent: 0 Rows_examined: 124948974 use statsdb; SELECT count(distinct Visits.visitorid) as 'uniques' FROM Visits,Visitors WHERE Visits.visitorid=Visitors.visitorid and candidateid in (32) and visittime>=1275721200 and visittime<=1275807599 and (omit=0 or omit>=1275807599) AND Visitors.segmentid=9 AND Visits.visitorid NOT IN (SELECT Visits.visitorid FROM Visits,Visitors WHERE Visits.visitorid=Visitors.visitorid and candidateid in (32) and visittime<1275721200 and (omit=0 or omit>=1275807599) AND Visitors.segmentid=9); It's basically counting unique visitors, and it's doing that by counting the visitors for today and then substracting those that have been here before. If you know of a better way to do this, let me know. I just don't understand why sometimes it can be so quick, and other times takes so long - even with the same exact query under the same server load. Here's the EXPLAIN on this query. As you can see it's using the indexes I've set up: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY Visits range visittime_visitorid,visitorid visittime_visitorid 4 NULL 82500 Using where; Using index 1 PRIMARY Visitors eq_ref PRIMARY,cand_visitor_omit PRIMARY 8 statsdb.Visits.visitorid 1 Using where 2 DEPENDENT SUBQUERY Visits ref visittime_visitorid,visitorid visitorid 8 func 1 Using where 2 DEPENDENT SUBQUERY Visitors eq_ref PRIMARY,cand_visitor_omit PRIMARY 8 statsdb.Visits.visitorid 1 Using where I tried to optimize the query a few weeks ago and came up with a variation that consistently took about 2 seconds, but in practice it ended up taking more time since 90% of the time the old query returned much quicker. Two seconds per query is too long because we are calling the query up to 50 times per page load, with different time periods. Could the quick behavior be due to the query being saved in the query cache? I tried running 'RESET QUERY CACHE' and 'FLUSH TABLES' between my benchmark tests and I was still getting quick results most of the time. Note: last night while running the query I got an error: Unable to save result set. My initial research shows that may be due to a corrupt table that needs repair. Could this be the reason for the behavior I'm seeing? In case you want server info: Accessing via PHP 4.4.4 MySQL 4.1.22 All tables are InnoDB We run optimize table on all tables weekly The sum of both the tables used in the query is 500 MB MySQL config: key_buffer = 350M max_allowed_packet = 16M thread_stack = 128K sort_buffer = 14M read_buffer = 1M bulk_insert_buffer_size = 400M set-variable = max_connections=150 query_cache_limit = 1048576 query_cache_size = 50777216 query_cache_type = 1 tmp_table_size = 203554432 table_cache = 120 thread_cache_size = 4 wait_timeout = 28800 skip-external-locking innodb_file_per_table innodb_buffer_pool_size = 3512M innodb_log_file_size=100M innodb_log_buffer_size=4M

  Read the article

• MySQL bindings for Rails 2.3.5 on Mac OS X 10.5.8

  - by lach

  I have a rails environment which I set-up with macports. I recently updated macports which seems to have had the side effect of breaking rails. When I try to boot a rails server I get: $ ./script/server => Booting WEBrick => Rails 2.3.5 application starting on http://0.0.0.0:3000 /opt/local/lib/ruby/gems/1.8/gems/rails-2.3.5/lib/rails/gem_dependency.rb:119:Warning: Gem::Dependency#version_requirements is deprecated and will be removed on or after August 2010. Use #requirement !!! The bundled mysql.rb driver has been removed from Rails 2.2. Please install the mysql gem and try again: gem install mysql. /opt/local/lib/ruby/vendor_ruby/1.8/i686-darwin9/mysql.bundle: dlopen(/opt/local/lib/ruby/vendor_ruby/1.8/i686-darwin9/mysql.bundle, 9): Library not loaded: /opt/local/lib/mysql5/mysql/libmysqlclient.15.dylib (LoadError) Referenced from: /opt/local/lib/ruby/vendor_ruby/1.8/i686-darwin9/mysql.bundle Reason: image not found - /opt/local/lib/ruby/vendor_ruby/1.8/i686-darwin9/mysql.bundle I've tried reinstalling the mysql gem many times using various configurations I've found around the web but nothing seems to help. Also, when I try to use rake I get: rake db:migrate Rails requires RubyGems >= 1.3.1 (you have 1.0.1). Pleasegem update --systemand try again. Even though: gem --version 1.3.6 What's going on here?

  Read the article

• Is it safe to set MySQL isolation to "Read Uncommitted" (dirty reads) for typical Web usage? Even with replication?

  - by Continuation

  I'm working on a website with typical CRUD web usage pattern: similar to blogs or forums where users create/update contents and other users read the content. Seems like it's OK to set the database's isolation level to "Read Uncommitted" (dirty reads) in this case. My understanding of the general drawback of "Read Uncommitted" is that a reader may read uncommitted data that will later be rollbacked. In a CRUD blog/forum usage pattern, will there ever be any rollback? And even if there is, is there any major problem with reading uncommitted data? Right now I'm not using any replication, but in the future if I want to use replication (row-based, not statement-based) will a "Read Uncommitted" isolation level prevent me from doing so? What do you think? Has anyone tried using "Read Uncommitted" on their RDBMS?

  Read the article

• GoldenGate 12c - MySQL Active-Active Replication Setup

  - by Jinyu Wang-Oracle

  Active-active  (also called Master-Master or Bi-Directional) replication captures data changes from two or more systems and replicat the changes to synchronize the data.  Active-Active replication is often needed for high availability, load balancing and scaling out purposes.   Oracle GoldenGate is known to be one of the first and the best replication tool handling active-active replications. As of Oracle GoldenGate 12c, it provides (Refer to Oracle GoldenGate 12.1.2 Documentation - Configuring Oracle GoldenGate for Active-Active High Availability for more information) the followings: Robust loop-back prevention Comprehensive conflict resolution and detection support Heterogeneous support across different database versions and operation systems.  Oracle GoldenGate supports active-active configurations for DB2 on z/OS, LUW, and IBM i, MySQL, Oracle, SQL/MX,SQL Server, Sybase, and Teradata. However, the setup is different from database to database. In this example, I will show you how to setup an active-active data replication between two MySQL database instances. The example setup below is to have active-active replication between MySQL 5.5 and MySQL 5.6 instances and is shown as follows: MySQL 5.5 (Manager Port: 15105)  Extract EXTRACT demoex01 SETENV (MYSQL_UNIX_PORT='/home/oracle/software/mysql_5.5.38/data/mysql.sock') DBOPTIONS CONNECTIONPORT 3305 DBOPTIONS HOST oraclelinux6.localdomain SOURCEDB test USERID root, PASSWORD mysql EXTTRAIL ./dirdat/extract/de TRANLOGOPTIONS ALTLOGDEST "/home/oracle/software/mysql_5.5.38/data/binlog/bin-log.index" FILTERTABLE test.checkpoint_tbl REPORTROLLOVER AT 05:30 ON saturday TABLE test.TCUSTMER; TABLE test.TCUSTORD; Pump EXTRACT demopm01 RMTHOST localhost, MGRPORT 15106, COMPRESS, TIMEOUT 30 RMTTRAIL ./dirdat/replicat/ps PASSTHRU TABLE test.TCUSTMER; TABLE test.TCUSTORD; Replicat replicat demorp01 setenv (MYSQL_UNIX_PORT='/home/oracle/software/mysql_5.5.38/data/mysql.sock') dboptions host oraclelinux6.localdomain, connectionport 3305 targetdb test, userid root, password mysql sourcedefs ./dirdat/replicat/democust.def discardfile ./dirrpt/demprp01.dsc, purge REPERROR (DEFAULT, ABEND) REPERROR(1062, IGNORE) map test.TCUSTMER, target test.TCUSTMER,colmap(usedefaults, region_code="region code"); map test.TCUSTORD, target test.TCUSTORD; MySQL 5.6 (Manager Port: 15106) Replicat replicat demorp01 setenv (MYSQL_UNIX_PORT='/home/oracle/software/mysql_5.6.19/data/mysql.sock') dboptions host oraclelinux6.localdomain, connectionport 3306 targetdb test, userid root, password mysql --assumetargetdefs sourcedefs ./dirdat/replicat/democust.def discardfile ./dirrpt/demprp01.dsc, purge map test.TCUSTMER, target test.TCUSTMER, colmap(usedefaults, "region code"=region_code); map test.TCUSTORD, target test.TCUSTORD; Extract EXTRACT demoex01 SETENV (MYSQL_UNIX_PORT='/home/oracle/software/mysql_5.6.19/data/mysql.sock') DBOPTIONS CONNECTIONPORT 3306 DBOPTIONS HOST oraclelinux6.localdomain SOURCEDB test USERID root, USERID mysql EXTTRAIL ./dirdat/extract/de TRANLOGOPTIONS ALTLOGDEST "/usr/local/mysql56/data/binlog/bin-log.index" FILTERTABLE test.checkpoint_tbl TABLE test.TCUSTMER; TABLE test.TCUSTORD; Pump EXTRACT demopm01 RMTHOST localhost, MGRPORT 15105, COMPRESS, TIMEOUT 30 RMTTRAIL ./dirdat/replicat/ps PASSTHRU TABLE test.TCUSTMER; TABLE test.TCUSTORD; The setup parameters are quite self-explanatory. The key setup is to avoid the replication data  looping. Oracle GoldenGate for MySQL uses the information in the replication checkpoint table to identify the transaction applied by replicats and thus avoid extracting those transactions by Oracle GoldenGate extracts. The example setup in the extract in MySQL 5.5 instance is shown as follows.  TRANLOGOPTIONS ALTLOGDEST "/home/oracle/software/mysql_5.5.38/data/binlog/bin-log.index" FILTERTABLE test.checkpoint_tbl Setting up an active-active replication is often more complicated than this and requires the following additional considerations. I would elaborate on this in the follow-up discussions. 

  Read the article

• MySQL full text search with partial words

  - by Rob

  MySQL Full Text searching appears to be great and the best way to search in SQL. However, I seem to be stuck on the fact that it won't search partial words. For instance if I have an article titled "MySQL Tutorial" and search for "MySQL", it won't find it. Having done some searching I found various references to support for this coming in MySQL 4 (i'm using 5.1.40). I've tried using "MySQL" and "%MySQL%", but neither works (one link I found suggested it was stars but you could only do it at the end or the beginning not both). Here's my table structure and my query, if someone could tell me where i'm going wrong that would be great. I'm assuming partial word matching is built in somehow. CREATE TABLE IF NOT EXISTS `articles` ( `article_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `article_name` varchar(64) NOT NULL, `article_desc` text NOT NULL, `article_link` varchar(128) NOT NULL, `article_hits` int(11) NOT NULL, `article_user_hits` int(7) unsigned NOT NULL DEFAULT '0', `article_guest_hits` int(10) unsigned NOT NULL DEFAULT '0', `article_rating` decimal(4,2) NOT NULL DEFAULT '0.00', `article_site_id` smallint(5) unsigned NOT NULL DEFAULT '0', `article_time_added` int(10) unsigned NOT NULL, `article_discussion_id` smallint(5) unsigned NOT NULL DEFAULT '0', `article_source_type` varchar(12) NOT NULL, `article_source_value` varchar(12) NOT NULL, PRIMARY KEY (`article_id`), FULLTEXT KEY `article_name` (`article_name`,`article_desc`,`article_link`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; INSERT INTO `articles` VALUES (1, 'MySQL Tutorial', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 6, 3, 1, '1.50', 1, 1269702050, 1, '0', '0'), (2, 'How To Use MySQL Well', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 1, 2, 0, '3.00', 1, 1269702050, 1, '0', '0'), (3, 'Optimizing MySQL', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 1, 0, '3.00', 1, 1269702050, 1, '0', '0'), (4, '1001 MySQL Tricks', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 1, 0, '3.00', 1, 1269702050, 1, '0', '0'), (5, 'MySQL vs. YourSQL', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 2, 0, '3.00', 1, 1269702050, 1, '0', '0'), (6, 'MySQL Security', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 2, 0, '3.00', 1, 1269702050, 1, '0', '0'); SELECT count(a.article_id) FROM articles a WHERE MATCH (a.article_name, a.article_desc, a.article_link) AGAINST ('mysql') GROUP BY a.article_id ORDER BY a.article_time_added ASC The prefix is used as it comes from a function that sometimes adds additional joins. As you can see a search for MySQL should return a count of 6, but unfortunately it doesn't.

  Read the article

• I am not able to create foreign key in mysql Error 150. Please help

  - by Shantanu Gupta

  i am trying to create a foreign key in my table. But when i executes my query it shows me error 150 Error Code : 1005 Can't create table '.\vts#sql-6ec_1.frm' (errno: 150) (0 ms taken) My Queries are Query to create a foreign Key alter table `vts`.`tblguardian` add constraint `FK_tblguardian` FOREIGN KEY (`GuardianPickPointId`) REFERENCES `tblpickpoint` (`PickPointId`) Primary Key table CREATE TABLE `tblpickpoint` ( `PickPointId` int(4) NOT NULL auto_increment, `PickPointName` varchar(500) default NULL, `PickPointLabel` varchar(500) default NULL, `PickPointLatLong` varchar(100) NOT NULL, PRIMARY KEY (`PickPointId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC Foreign Key Table CREATE TABLE `tblguardian` ( `GuardianId` int(4) NOT NULL auto_increment, `GuardianName` varchar(500) default NULL, `GuardianAddress` varchar(500) default NULL, `GuardianMobilePrimary` varchar(15) NOT NULL, `GuardianMobileSecondary` varchar(15) default NULL, `GuardianPickPointId` varchar(100) default NULL, PRIMARY KEY (`GuardianId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

  Read the article

• Can MYSQL filter by date if date is stored as text? ex "02/10/1984"

  - by Roeland

  Hello! I am trying to modify an app for a client which has already a database of over 1000 items. The dates are stored as text in the database with the format "02/10/1984". The system allows you to add and remove fields to the catalog dynamically and it also allows the advanced search to have specific fields be allowed. The problem is that it wasn't designed with dates in mind, so when I set a field as a date, and try to search by a range the query is trying to do a AND (cfv0.value = 01/02/2004 AND cfv0.value <= 05/03/2008) . I can make it so the date range passed is a numeric time value. Is there a way that when sending the query, it takes the text fields (with the date) and converts it to numeric time value so at that point I am basically just comparing numbers which would work fine. I do not have the option to change all the current date to numeric value due to the way the dynamic fields are set up. Thanks guys!

  Read the article

• Is there a way to optimize this update query?

  - by SchlaWiener

  I have a master table called "parent" and a related table called "childs" Now I run a query against the master table to update some values with the sum from the child table like this. UPDATE master m SET quantity1 = (SELECT SUM(quantity1) FROM childs c WHERE c.master_id = m.id), quantity2 = (SELECT SUM(quantity2) FROM childs c WHERE c.master_id = m.id), count = (SELECT COUNT(*) FROM childs c WHERE c.master_id = m.id) WHERE master_id = 666; Which works as expected but is not a good style because I basically make multiple SELECT querys on the same result. Is there a way to optimize that? (Making a query first and storing the values is not an option. I tried this: UPDATE master m SET (quantity1, quantity2, count) = ( SELECT SUM(quantity1), SUM(quantity2), COUNT(*) FROM childs c WHERE c.master_id = m.id ) WHERE master_id = 666; but that doesn't work.

  Read the article

• I am not able to drop foreign key in mysql Error 150. Please help

  - by Shantanu Gupta

  i am trying to create a foreign key in my table. But when i executes my query it shows me error 150 Error Code : 1005 Can't create table '.\vts#sql-6ec_1.frm' (errno: 150) (0 ms taken) My Queries are Query to create a foreign Key alter table `vts`.`tblguardian` add constraint `FK_tblguardian` FOREIGN KEY (`GuardianPickPointId`) REFERENCES `tblpickpoint` (`PickPointId`) EDIT: Now I am trying to drop this constraint But it fails again and shows me same error as it was giving when i was trying to create foreign key. alter table `vts`.`tblguardian` drop index `FK_tblguardian` Primary Key table CREATE TABLE `tblpickpoint` ( `PickPointId` int(4) NOT NULL auto_increment, `PickPointName` varchar(500) default NULL, `PickPointLabel` varchar(500) default NULL, `PickPointLatLong` varchar(100) NOT NULL, PRIMARY KEY (`PickPointId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC Foreign Key Table CREATE TABLE `tblguardian` ( `GuardianId` int(4) NOT NULL auto_increment, `GuardianName` varchar(500) default NULL, `GuardianAddress` varchar(500) default NULL, `GuardianMobilePrimary` varchar(15) NOT NULL, `GuardianMobileSecondary` varchar(15) default NULL, `GuardianPickPointId` int(4) default NULL, PRIMARY KEY (`GuardianId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

  Read the article

• How do I increase Relevance value in an advanced MySQL query?

  - by morgant

  I've got a MySQL query similar to the following: SELECT *, MATCH (`Description`) AGAINST ('+ipod +touch ' IN BOOLEAN MODE) * 8 + MATCH(`Description`) AGAINST ('ipod touch' IN BOOLEAN MODE) AS Relevance FROM products WHERE ( MATCH (`Description`) AGAINST ('+ipod +touch' IN BOOLEAN MODE) OR MATCH(`LongDescription`) AGAINST ('+ipod +touch' IN BOOLEAN MODE) ) HAVING Relevance > 1 ORDER BY Relevance DESC Now, I've made the query more advanced by also searching for UPC: SELECT *, MATCH (`Description`) AGAINST ('+ipod +touch ' IN BOOLEAN MODE) * 8 + MATCH(`Description`) AGAINST ('ipod touch' IN BOOLEAN MODE) + `UPC` = '123456789012' * 16 AS Relevance FROM products WHERE ( MATCH (`Description`) AGAINST ('+ipod +touch' IN BOOLEAN MODE) OR MATCH(`LongDescription`) AGAINST ('+ipod +touch' IN BOOLEAN MODE) ) AND `UPC` = '123456789012' HAVING Relevance > 1 ORDER BY Relevance DESC That'll return results, but the fact that I had a successful match on the UPC does not increase the value of Relevance. Can I only do that kind of calculation w/full text searches like MATCH() AGAINST()? Clarification: Okay, so my real question is, why does the following not have a Relevance = 16? SELECT `UPC`, `UPC` = '123456789012' * 16 AS Relevance FROM products WHERE `UPC` = '123456789012' HAVING Relevance > 1 ORDER BY Relevance DESC

  Read the article

• Why index_merge is not used here using MySQL?

  - by user198729

  Setup: mysql> create table t(a integer unsigned,b integer unsigned); mysql> insert into t(a,b) values (1,2),(1,3),(2,4); mysql> create index i_t_a on t(a); mysql> create index i_t_b on t(b); mysql> explain select * from t where a=1 or b=4; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t | ALL | i_t_a,i_t_b | NULL | NULL | NULL | 3 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ Is there something I'm missing? Update mysql> explain select * from t where a=1 or b=4; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t | ALL | i_t_a,i_t_b | NULL | NULL | NULL | 1863 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ Version: mysql> select version(); +----------------------+ | version() | +----------------------+ | 5.1.36-community-log | +----------------------+

  Read the article

• unable to install mysql completely on debian 5.0

  - by austin powers

  hi, its been a couple of days that I'm trying to install mysql on my vps which has debian 5.0 with 256mb ram. I've installed webmin also. here is the symptoms : after installing mysql using either webmin or apt-get I am trying to connect to mysql for changing root password but every time I cope with this error : ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) so I start to investigate and I understand there is no root user inside mysql database when I use : UPDATE user SET password=PASSWORD('newpassword') WHERE user="root"; it says 0 row affected I reinstall mysql for several times but the same problem still exits. please help me how can I install mysql-server as well as mysql-client correctly. regards.

  Read the article

• MySQL driver for Rails in Windows 7 x64

  - by Darth

  I've got problem with connecting to MySQL database on my freshly installed Windows 7 machine. I'm getting this error when I try to migrate my database. !!! The bundled mysql.rb driver has been removed from Rails 2.2. Please install the mysql gem and try again: gem install mysql. rake aborted! 193: %1 is not valid Win32 application - C:/Ruby/lib/ruby/gems/1.8/gems/mysql-2.8.1-x86-mswin32/lib/1.8/mysql_api.so I currently have installed ruby 1.8.6 (2008-08-11 patchlevel 287) [i386-mswin32] mysql version 5.0.86 for Win64 gem 1.3.1 mysql-2.8.1-x86-mswin32

  Read the article

• Who should I run mysql as, on a personal computer?

  - by user664833

  I just installed mysql via homebrew (with brew install mysql, on Mac OS X Mountain Lion - recently installed from scratch). Following the installation, there is a "caveats" section with options around further necessary actions to take: ==> Caveats Set up databases to run AS YOUR USER ACCOUNT with: unset TMPDIR mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp To set up base tables in another folder, or use a different user to run mysqld, view the help for mysqld_install_db: mysql_install_db --help and view the MySQL documentation: * http://dev.mysql.com/doc/refman/5.5/en/mysql-install-db.html * http://dev.mysql.com/doc/refman/5.5/en/default-privileges.html To run as, for instance, user "mysql", you may need to `sudo`: sudo mysql_install_db ...options... Start mysqld manually with: mysql.server start Note: if this fails, you probably forgot to run the first two steps up above A "/etc/my.cnf" from another install may interfere with a Homebrew-built server starting up correctly. To connect: mysql -uroot To launch on startup: * if this is your first install: mkdir -p ~/Library/LaunchAgents cp /usr/local/Cellar/mysql/5.5.27/homebrew.mxcl.mysql.plist ~/Library/LaunchAgents/ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist * if this is an upgrade and you already have the homebrew.mxcl.mysql.plist loaded: launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist cp /usr/local/Cellar/mysql/5.5.27/homebrew.mxcl.mysql.plist ~/Library/LaunchAgents/ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist You may also need to edit the plist to use the correct "UserName". On previous versions of Mac OS X I ran mysql as mysql user, but now I am confronted by the idea of running it as myself. I am the only one who uses this computer (which happens to be my laptop), and I do programming for work and for pleasure. What are the pros & cons, or best practices, around choosing whether to run mysql AS YOUR USER ACCOUNT or as mysql or something else still?

  Read the article

• How to order by results from 2 seperate tables in PHP and MySQL.

  - by Vafello

  I am trying to output results of 2 sql queries to one JSON file. The problem is that I would like to order them ascending by distance which is the result of equation that takes homelat and homelon from the users table and lat, lng from locations table.(basically it takes lattitude and longitude of one point and another and computes the distance between these points). Is it possible to take some parameters from both select queries, compute it and output the result in ascending order? $wynik = mysql_query("SELECT homelat, homelon FROM users WHERE guid='2'") or die(mysql_error()); ; $query = "SELECT * FROM locations WHERE timestamp"; $result = map_query($query); $points = array(); while ($aaa = mysql_fetch_assoc($wynik)) { while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { array_push($points, array('name'=>$row['name'], 'lat'=>$row['lat'], 'lng'=>$row['lng'], 'description'=>$row['description'], 'eventType'=>$row['eventType'], 'date'=>$row['date'], 'isotime'=>date('c', ($row['timestamp'])), 'homelat'=>$aaa['homelat'], 'homelon'=>$aaa['homelon'])); } echo json_encode(array("Locations"=>$points));

  Read the article

• MySQL, return only rows where there are duplicates among two columns.

  - by Richard Waite

  I have a table in MySQL of contact information ; first name, last name, address, etc. I would like to run a query on this table that will return only rows with first and last name combinations which appear in the table more than once. I do not want to group the "duplicates" (which may only be duplicates of the first and last name, but not other information like address or birthdate) - I want to return all the "duplicate" rows so I can look over the results and determine if they are dupes or not. This seemed like it would be a simple thing to do, but it has not been. Every solution I can find either groups the dupes and gives me a count only (which is not useful for what I need to do with the results) or doesn't work at all. Is this kind of logic even possible in a query ? Should I try and do this in Python or something?

  Read the article

• MySQL table data question?

  - by mySQL

  How should my Mysql table data look like for a single checkbox that checks and see if the user has said yes if its clicked or no if its not? Here is the checkbox. <input type="checkbox" name="yes" id="yes" value="yes" /> I was wondering how would I add it to the following table. CREATE TABLE IF NOT EXISTS `vote` ( `counter` int(8) NOT NULL default '0', `value` int(8) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

  Read the article

• Multiple connections to a MySQL database in a single PHP script.

  - by st3

  There are multiple times in one page where I need to connect and subsequently query a MySQL database, yet my code won't let me. I think it might be something to do with how my files are nested but it makes no sense. I am opening the SQL connection in the header file. The top of the offending page looks like the following: <?php $page_title = 'Dashboard'; include('templates/header.inc'); // includes a 'require_once('mysqli_connect.php') and a small query to the database; require_once('includes/functions.php'); require_once('includes/dashboard_sql.php'); // Contains functions which connect to database (which are failing.) ?> I get the PHP error Notice: Undefined variable: dbc in /Library/WebServer/Documents/pediatory_site/includes/dashboard_sql.php Where $dbc is the database connection defined in mysqli_connect.php. If anyone could help me out that would be great.

  Read the article

• How to return null value if the query has no corresponding value?

  - by Holicreature

  Hi i've a query select c.name as companyname, u.name,u.email,u.role,a.date from useraccount u, company c, audittrial a where u.status='active' and u.companyid=c.id and (u.companyid=a.companyID and a.activity like 'User activated%' and a.email=u.email) order by u.companyid desc limit 10 So if the following part doesnt't satisfy, (u.companyid=a.companyID and a.activity like 'User activated%' and a.email=u.email) no rows will be returned.. but i want to return the result of the following query select c.name as companyname, u.name,u.email,u.role,a.date from useraccount u, company c, audittrial a where u.status='active' and u.companyid=c.id order by u.companyid desc limit 10 but to add that, i should return the date if available and return null value if date is not available.. how can i do this?

  Read the article

• How to create a MySQL query for time based elements with a 'safe window'?

  - by pj4533

  I am no SQL expert, far from it. I am writing a Rails application, and I am new at that as well. I come from a desktop programming background. My application has a table of data, one of the columns is the time at which the data was logged. I want to create a query with a 'safe window' around EACH row. By that I mean, it returns the first row, then for X minutes (based on the timelogged column) it won't return any data, once X minutes is up, it will return the next row. For example: ID | TimeLogged 1 | 3/5/2010 12:01:01 2 | 3/5/2010 12:01:50 3 | 3/5/2010 12:02:03 4 | 3/5/2010 12:10:30 5 | 3/5/2010 01:30:03 6 | 3/5/2010 01:31:05 With a 'safe window' of 5 minutes I want to create a query to return: 1 | 3/5/2010 12:01:01 4 | 3/5/2010 12:10:30 5 | 3/5/2010 01:30:03 (It skipped the 12:01:50 and 12:02:03 items because they occurred within 5 minutes of the first item.) Another example, with a 'safe window' of 15 minutes I want to return: 1 | 3/5/2010 12:01:01 5 | 3/5/2010 01:30:03 Perhaps I have to just return all data and parse it myself?

  Read the article

• Can I run mysqld on top of glusterfs?

  - by Richard Holloway

  I have been playing with glusterfs recently. What I want to try is to run mysqld on top of the glusterfs in a similar way as it is possible to run MySQL on top of DRBD. I am familiar with MySQL replication and the advantages of using that instead of this approach and I am also aware of MongoDB and other NoSQL solutions. However, it would be an easy solution to a few specific projects I have coming up if I could leave MySQL as it is and replicate the underlying file system. Is this possible and if it is where can I find out how?

  Read the article

• I'm using spring, hibernate and mysql. How can I let my app create the table automatically.

  - by Yousui

  Hi guys, I'm using spring, all my annotated entity class information are put in the ApplicationContext.xml. I'm using a MySql database, now how can I use the SchemaExport function in hibernate to create the tables? My application can't create the table automatically, though i have set <prop key="hbm2ddl.auto">create</prop>. This is my ApplicationContext.xml: <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd"> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql:///edde" /> <property name="username" value="root" /> <property name="password" value="" /> </bean> <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="annotatedClasses"> <list> <value>com.edde.Book</value> </list> </property> <property name="hibernateProperties"> <props> <prop key="current_session_context_class">thread</prop> <prop key="show_sql">true</prop> <prop key="hbm2ddl.auto">create</prop> <prop key="hibernate.show_sql">true</prop> <prop key="hibernate.use_sql_comments">true</prop> <prop key="hibernate.format_sql">true</prop> </props> </property> </bean> <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"> <property name="sessionFactory" ref="sessionFactory" /> </bean> <tx:annotation-driven transaction-manager="transactionManager" /> <bean id="bookDao" class="com.edde.BookDao"> <property name="sessionFactory" ref="sessionFactory"></property> </bean> <bean id="bookService" class="com.edde.BookServiceImpl"> <property name="dao" ref="bookDao"></property> </bean> </beans> This is my test application: package com.edde; import org.springframework.context.support.ClassPathXmlApplicationContext; public class Test { /** * @param args */ public static void main(String[] args) { ClassPathXmlApplicationContext factory = new ClassPathXmlApplicationContext("applicationContext.xml"); BookService service = (BookService) factory.getBean("bookService"); Book book = new Book(); book.setBookName("great book"); service.saveBook(book); } } When I run my app, I get this error: 2010-05-20 17:25:20,777 DEBUG [org.hibernate.SQL] - /* insert com.edde.Book */ insert into Book (author, bookName, publication) values (?, ?, ?) Hibernate: /* insert com.edde.Book */ insert into Book (author, bookName, publication) values (?, ?, ?) 2010-05-20 17:25:20,809 DEBUG [org.hibernate.jdbc.AbstractBatcher] - about to close PreparedStatement (open PreparedStatements: 1, globally: 1) 2010-05-20 17:25:20,809 DEBUG [org.hibernate.util.JDBCExceptionReporter] - could not insert: [com.edde.Book] [/* insert com.edde.Book */ insert into Book (author, bookName, publication) values (?, ?, ?)] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'edde.book' doesn't exist at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.Util.getInstance(Util.java:384) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2398) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2316) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2301) at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:94) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2329) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2836) at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:268) at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:321) at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:204) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:130) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210) at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195) at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93) at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:705) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:693) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:689) at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:686) at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:1) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406) at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:683) at com.edde.BookDao.insertBook(BookDao.java:18) at com.edde.BookServiceImpl.saveBook(BookServiceImpl.java:34) at com.edde.Test.main(Test.java:17) 2010-05-20 17:25:20,809 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1146, SQLState: 42S02 2010-05-20 17:25:20,809 ERROR [org.hibernate.util.JDBCExceptionReporter] - Table 'edde.book' doesn't exist 2010-05-20 17:25:20,809 DEBUG [org.springframework.orm.hibernate3.SessionFactoryUtils] - Closing Hibernate Session 2010-05-20 17:25:20,809 DEBUG [org.hibernate.jdbc.ConnectionManager] - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)] 2010-05-20 17:25:20,809 DEBUG [org.hibernate.jdbc.ConnectionManager] - transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources! Exception in thread "main" org.springframework.dao.InvalidDataAccessResourceUsageException: could not insert: [com.edde.Book]; SQL [/* insert com.edde.Book */ insert into Book (author, bookName, publication) values (?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not insert: [com.edde.Book] at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:629) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411) at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:683) at com.edde.BookDao.insertBook(BookDao.java:18) at com.edde.BookServiceImpl.saveBook(BookServiceImpl.java:34) at com.edde.Test.main(Test.java:17) Caused by: org.hibernate.exception.SQLGrammarException: could not insert: [com.edde.Book] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:64) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2329) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2836) at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:268) at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:321) at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:204) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:130) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210) at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195) at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93) at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:705) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:693) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:689) at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:686) at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:1) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406) ... 5 more Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'edde.book' doesn't exist at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:513) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.Util.getInstance(Util.java:384) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2398) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2316) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2301) at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:94) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57) ... 23 more Now my question is: how to create the table automatically? Thanks.

  Read the article

• MySQL 1064 error, works in command line and phpMyAdmin; not in app

  - by hundleyj

  Here is my query: select * from (select *, 3956 * 2 * ASIN(SQRT(POWER(SIN(RADIANS(45.5200077 - lat)/ 2), 2) + COS(RADIANS(45.5200077)) * COS(RADIANS(lat)) * POWER(SIN(RADIANS(-122.6942014 - lng)/2),2))) AS distance from stops order by distance, route asc) as p group by route, dir order by distance asc limit 10 This works fine at the command line and in PHPMyAdmin. I'm using Dbslayer to connect to MySQL via my JavaScript backend, and the request is returning a 1064 error. Here is the encoded DBSlayer request string: http://localhost:9090/db?{%22SQL%22:%22select%20*%20from%20%28select%20*,%203956%20*%202%20*%20ASIN%28SQRT%28POWER%28SIN%28RADIANS%2845.5200077%20-%20lat%29/%202%29,%202%29%20+%20COS%28RADIANS%2845.5200077%29%29%20*%20COS%28RADIANS%28lat%29%29%20*%20POWER%28SIN%28RADIANS%28-122.6942014%20-%20lng%29/2%29,2%29%29%29%20AS%20distance%20from%20%60stops%60%20order%20by%20%60distance%60,%20%60route%60%20asc%29%20as%20p%20group%20by%20%60route%60,%20%60dir%60%20order%20by%20%60distance%60%20asc%20limit%2010%22} And the response: {"MYSQL_ERRNO" : 1064 , "MYSQL_ERROR" : "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 '(RADIANS(45.5200077)) * COS(RADIANS(lat)) * POWER(SIN(RADIANS(-122.6942014 - lng' at line 1" , "SERVER" : "trimet"} Thanks!

  Read the article

• Anything wrong with this MySQL quert? takes 10 seconds+ to load

  - by user345426

  I have a search that is taking 10 seconds+ to execute! Keep in mind it is also searching over 200,000 products in the database. I posted the explain and MySQL query here. 1 SIMPLE p ref PRIMARY,products_status,prod_prodid_status,product... products_status 1 const 9048 Using where; Using temporary; Using filesort 1 SIMPLE v ref PRIMARY,vendors_id,vendors_vendorid vendors_vendorid 4 rhinomar_rhinomartnew.p.vendors_id 1 1 SIMPLE s ref products_id products_id 4 rhinomar_rhinomartnew.p.products_id 1 1 SIMPLE pd ref PRIMARY,products,prod_desc_prodid_prodname prod_desc_prodid_prodname 4 rhinomar_rhinomartnew.p.products_id 1 1 SIMPLE p2c ref PRIMARY,ptc_catidx PRIMARY 4 rhinomar_rhinomartnew.p.products_id 1 Using where; Using index 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 rhinomar_rhinomartnew.p2c.categories_id 1 Using where MySQL Query: select p.products_id, p.products_image, p.products_price, p.products_weight, p.products_unit_quantity, s.specials_new_products_price, s.status, pd.products_name, pd.products_img_alt from products p left join vendors v ON v.vendors_id = p.vendors_id left join specials s on s.products_id = p.products_id left join products_description pd on pd.products_id = p.products_id left join products_to_categories p2c on p2c.products_id = p.products_id left join categories c on c.categories_id = p2c.categories_id where ( ( pd.products_name like '%apparel%' ) or p2c.categories_id IN (773, 132, 135, 136, 119, 122, 124, 125, 126, 1749, 1753, 1747, 123, 127, 130, 131, 178, 137, 140, 164, 165, 166, 167, 168, 169, 832, 2045 ) or p.products_id = 'apparel' or p.products_model = 'apparel' or CONCAT(v.vendors_prefix, '-') = 'apparel' or CONCAT( v.vendors_prefix, '-', p.products_id ) = 'apparel' ) and p.products_status = '1' and c.categories_status = '1' group by p.products_id order by pd.products_name

  Read the article

• How to combine two rows and calculate the time difference between two timestamp values in MySQL?

  - by Nadar

  I have a situation that I'm sure is quite common and it's really bothering me that I can't figure out how to do it or what to search for to find a relevant example/solution. I'm relatively new to MySQL (have been using MSSQL and PostgreSQL earlier) and every approach I can think of is blocked by some feature lacking in MySQL. I have a "log" table that simply lists many different events with their timestamp (stored as datetime type). There's lots of data and columns in the table not relevant to this problem, so lets say we have a simple table like this: CREATE TABLE log ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(16), ts DATETIME NOT NULL, eventtype VARCHAR(25), PRIMARY KEY (id) ) Let's say that some rows have an eventtype = 'start' and others have an eventtype = 'stop'. What I want to do is to somehow couple each "startrow" with each "stoprow" and find the time difference between the two (and then sum the durations per each name, but that's not where the problem lies). Each "start" event should have a corresponding "stop" event occuring at some stage later then the "start" event, but because of problems/bugs/crashed with the data collector it could be that some are missing. In that case I would like to disregard the event without a "partner". That means that given the data: foo, 2010-06-10 19:45, start foo, 2010-06-10 19:47, start foo, 2010-06-10 20:13, stop ..I would like to just disregard the 19:45 start event and not just get two result rows both using the 20:13 stop event as the stop time. I've tried to join the table with itself in different ways, but the key problems for me seems to be to find a way to correctly identify the corresponding "stop" event to the "start" event for the given "name". The problem is exactly the same as you would have if you had table with employees stamping in and out of work and wanted to find out how much they actually were at work. I'm sure there must be well known solutions to this, but I can't seem to find them...

  Read the article

< Previous Page | 56 57 58 59 60 61 62 63 64 65 66 67  | Next Page >