mysql

MYSQL OR vs IN performance

I am wondering if there is any difference in regards to performance between the following SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4) SELECT ... FROM ... WHERE someFIELD between 0 AND 5 SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ... or will MySQL optimize the SQL in the same way compilers will opt...

identify mysql problem

Hi There I've write a web-application in PHP which has 30 tables+views. From time to time my application doesn't work and think this is related to the mysql db. Unfortunately I can't see the errors from the browser on that server because php.ini says so. Also when I try to connect to mysql db using phpMyAdmin the connection fails wh...

PHP: Checking for valid MySQL result resource

I have this code: $rows = array(); $res = mysql_query($someQuery); if(!mysql_errno()) while($row = mysql_fetch_assoc($res)) $rows[] = $row; some query is an arbitrary query that I write in to a form. The mysql_errno catches the case when I write a mysql query with errors in it. But, I just discovered that when I do a "Del...

Where can I get a security code review for free?

Dear Abby, I've been working on a PHP website which utilizes a MySQL database (using PDO). I would just like to know it is secure before I go ahead and release it to the public. It is a free, non-profit website so I don't really plan on making money from it and therefore I would rather not invest hundreds or thousands of dollars for a s...

monitoring MySQL programmatically? SNMP?

I've been asked to see if we can monitor the "health" of mysql server in some way. The proposal was that there might be snmp support (this is for version 5.0.x). I was not able to find definitive information on it - A link to a reference would be great. The real issue is to get periodic information on the "health" or status of mysql. ...

Using IF statements in a MySQL trigger to insert multiple rows

I have a trigger that stores changes made in a separate table when a user edits the data. This data is written out on a web page beneath the current data in human readable format, i.e. 23/04/09 22:47 James Smith changed Tenant Name from "George Hill" to "George Hilling". The trigger I have looks a bit like this - (this is the abridged...

Can i reuse bind variable in an 'insert ... on duplicate update ...' statement?

I am attempting to run a query that uses bind variables against a mysql database engine. I am wondering how I can tell the engine to "reset" the bind variable assignments. I'm sure an example will explain much better than my poor brain. Here is the query: INSERT INTO site_support_docs ( ASSET_ID, TIME_STAMP, ...

Confusing mysql problem

I have got a problem with two tables: CREATE TABLE IF NOT EXISTS `addresses` ( `adr_id` int(11) NOT NULL auto_increment, `per_id` int(11) NOT NULL, `adr_street` varchar(50) NOT NULL, `adr_houseno` int(11) default NULL, `adr_housenoadd` varchar(10) default NULL, `adr_postalcode` varchar(25) NOT NULL, `adr_city` varc...

Function for week of the month in mysql

I was looking for a simple function to get the week of the month (rather than the easy week of the year) in a mysql query. The best I could come up with was: WEEK(dateField) - WEEK(DATE_SUB(dateField, INTERVAL DAYOFMONTH(dateField)-1 DAY)) + 1 I'd love to know if I'm reinventing the wheel here, and if there is an easier and cleaner s...

Does the order of tables referenced in the ON clause of the JOIN matter?

Does it matter which way I order the criteria in the ON clause for a JOIN? select a.Name, b.Status from a inner join b on a.StatusID = b.ID versus select a.Name, b.Status from a inner join b on b.ID = a.StatusID Is there any impact on performance? What if I had multiple criteria? Is one order more maintainable than another? ...

SELECT WHERE field!=value how it's done in mysql?

I can't find the answer since searching mysql NOT in google is a nightmare (even with the quotes). I need to make a query like this: SELECT * FROM table WHERE field=value AND field2!=value2 AND field3!=value3 How it is done? Is it even possible? ...

Select N rows from a table with a non-unique foreign key

I have asked a similar question before and while the answers I got were spectacular I might need to clearify. Just like This question I want to return N number of rows depending on a value in a column. My example will be I have a blog where I want to show my posts along with a preview of the comments. The last three comments to be exa...

How to scale MySQL with multiple machines?

I have a web app running LAMP. We recently have an increase in load and is now looking at solutions to scale. Scaling apache is pretty easy we are just going to have multiple multiple machines hosting it and round robin the incoming traffic. However, each instance of apache will talk with MySQL and eventually MySQL will be overloaded. ...

MySQL: Multi-column join across multiple tables?

I have a set of three tables: Dining_Tables; +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | dining_table | int(11) | NO | PRI | NULL | | | bus_boy ...

mySQL: select * in combination with UNIX_TIMESTAMP

Right now I'm retrieving data from my database as follows: SELECT id, UNIX_TIMESTAMP(cdate) as myunixdate, permalink, title FROM mytable But I would like to do it as follows, but it doesn't work. SELECT *, UNIX_TIMESTAMP(cdate) FROM mytable My question is, how can I combine UNIX_TIMESTAMP without having to specify all the other field...

Migrating technologies: PHP+MySQL -> ASP.NET MVC+MS SQL 2008

I have a completed web app in PHP 5 + MySQL. I have not yet started its conversion, but it will migrate to ASP.NET MVC + MS SQL 2008. I'm not sure how to progress for the easiest transition: Edit the PHP DAL for SQL Server. Migrate to the new db immediately Leave the live code alone. Create ASP.NET MVC with a MySQL DAL to use for now. ...

How to attach/restore MySQL database copy?

I need to convert a MySql Database to SQL server. I received the database in a zip file containing only *.frm, *.MYI and *.MYD. Im not familiar with MySQL. I downloaded and installed version 5.1 server and tools. I copied that folder containing the files to the folder where the other databases are located. Using MySql administrator, in C...

[Mysql] Fetch all news and all comments

Hi, I'm trying to code a query that gets all the news and all the comments for each news. My current query is : SELECT n.*, c.* AS comments FROM news n JOIN comments c ON (c.news_id = n.id) But when I fetch the query as an array it gives me a key by comment and I would like to have a key by news and all comments in a s...

Is there a mySQL equivalent to Postgresql's "Dollar-Quoted String Constants"?

In postgresql, it's possible to use "Dollar-Quoted String Constants" to avoid escaping single quotes in constant strings: Without Dollar-Quoted String Constants: 'Jeff''s table' With Dollar-Quoted String Constants: $$Jeff's table$$ Is there a MySQL equivalent? On edit: I'm not looking for a way to sanitize inputs, I'm looking for...

MySQL Selecting wrong column value in Group By query

Here's a real noobish MySQL query problem I'm having. I have a high score table in a game I'm writing. The high score DB records a name, level, and score achieved. There are many near duplicates in the db. For example: Name | Level | Score | Timestamp (key) Bob 2 41 | 1234567.890 Bob 3 15 | 1234568.890 Bob 3 ...