mysql

Question about mysql indexes on low to medium cardinality columns

I have a general question about the way that database indexing works, particularly in mysql. Let's say I have a table with a million rows with a column "ClientID" that is distributed relatively equally among 30 values. Thus, this column is very low cardinality (30) relative to the primary key (1 million). Now, I understand that you ...

Calculate time from timezones in php

Hai I have the system with employees having different timezones in their profile. I would like to show the date according to their timezones specified. The GMT time zone values are placed in the database. could you guys help me ...

mysql and .net: when using tableadapters I'm getting MySqlException "insert command denied for user", otherwise everything is fine

Hi! I am using mysql as db for my asp.net application. Here are the facts: I am using connection string from web.config which has both username and password. I can do SELECT with tableadapter. When I am trying to do INSERT with tableadapter, I am getting "mysqlexception insert command denied for user" error When I try to do INSERT pr...

What is optimal isolation level for MySql using InnoDB running Moodle 1.9.X

Which InnoDB isolation level should be used with Moodle 1.9.X. The default is REPEATABLE READ, is it save, however, to use READ COMMITTED for better performace? ...

Mysql optimization

I have this mysql table called comments which looks like this: commentID parentID type userID date comment The commentID is set as Primary key, but most of the time I fetch the data using the parentID. How should I set my indexes? Should I just add an index on parentID and let commentID be the primary key? Edit: Would it be bad to h...

Update table of two different database

This is the query to update table in one database that is of other Update test.temp a, test2.temp b Set a.name=b.name Where a.no=b.no; Now I don't want to write every field i.e a.name=b.name Is there any solution? ...

How can I remove duplicate numbers containing text?

I have a table that looks like this: |StreetName NR| NR | |Teststreet 34| 34 | How can i delete only the number in Streetname when it is the same in NR?? ...

Determine which table takes most disk space in MySQL

What is the easiest way to determine which table takes most disk space ? A handicap: I have no MySQL server, only the file with all data (dump.sql) ...

Monitoring used connections on mysql to debug 'too many connections'

On LAMP production server I get the 'too many connections' error from MYSQL occasionally, I want to add monitoring to find if the reason is that I exceed the max-connections limit. My question: How can I query from mysql or from mysqladmin the current number of used connections? (I noticed that show status gives total connections and n...

Mysql through PHP gives a "Can't create/write to file" error

The full error is: Message: Mysqli statement execute error : Can't create/write to file '/var/www/zendApp/backup/mysql/1268733580_name.sql' (Errcode: 13) I have tried several things already with the /var/www/ZendApp/backup/mysql directory $chmod 777 mysql $chmod 1777 mysql $chown root:root mysql $chown mysql:mysql mysql Bu...

maintaining query-oriented applications

I am currently doing some kind of reporting system.the figures, tables, graphs are all based on the result of queries. somehow i find that complex queries are not easy to maintain, especially when there are a lot of filtering. this makes the query very long and not easy to understand. And also, sometimes, queries with similar filters are...

What is optimal hardware configuration for heavy load LAMP application

I need to run Linux-Apache-PHP-MySQL application (Moodle e-learning platform) for a large number of concurrent users - I am aiming 5000 users. By concurrent I mean that 5000 people should be able to work with the application at the same time. "Work" means not only do database reads but writes as well. The application is not very typical...

"Unknown database 'mydb' " error with jpa

hello, i'm trying to connect to mysql server but get this error. when mydb is the only name in the system. please tell me where i'm wrong... my persistence xml is as folow: <persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/...

MySQL - optimising selection across two linked tables

I have two MySQL tables, states and trans: states (200,000 entries) looks like: id (INT) - also the primary key energy (DOUBLE) [other stuff] trans (14,000,000 entries) looks like: i (INT) - a foreign key referencing states.id j (INT) - a foreign key referencing states.id A (DOUBLE) I'd like to search for all entries in trans with...

Multiple foreign keys in one table to 1 other table in mysql

Hey guys, I got 2 tables in my database: user and call. User exists of 3 fields: id, name, number and call : id, 'source', 'destination', 'referred', date. I need to monitor calls in my app. The 3 ' ' fields above are actually userid numbers. now i'm wondering, can i make those 3 field foreign key elements of the id-field in table us...

count date difference in hours using php and mysql

Hi, How can i find the date difference in hours using php and mysql both. Thanks Avinash ...

Getting Phing's dbdeploy task to automatically rollback on delta error

I am using Phing's dbdeploy task to manage my database schema. This is working fine, as long as there is no errors in the queries of my delta files. However, if there is an error, dbdeploy will just run the delta files up to the query with the error and then abort. This causes me some frustration, because I have to manually rollback the...

mysql procedure to update numeric reference in previous rows when one is updated

There's a table like this one ______________________ | id | title | order | |----------------------| | 1 | test1 | 1 | |-----|--------|-------| | 2 | test2 | 2 | |-----|--------|-------| | 3 | test3 | 3 | |-----|--------|-------| | 4 | test4 | 4 | '----------------------' when i introduce in my mysql sh...

How do I write an SP in phpMyAdmin (MySQL)?

How do I write a stored procedure in phpMyAdmin? ...

In MATLAB can I convert a java boolean to a MATLAB logical?

In MATLAB I'm using a couple of java routines I've written to interface with a MyQSL database. One routine returns a boolean value result <1x1 java.lang.Boolean> >> result result = true When I then use it in a conditional statement I get an error message. >> if result, disp('result is true') end ??? Conversion to logical from java....