mysql

MySql NOT IN Performance problem

Hi, I am running the query below on a table with around 100k rows in it using a NOT IN and the performance is terrible. Is there another way of achieving the same result? SELECT c.Id, c.Name, c.address, c.town, c.county, c.postcode, FROM contractor AS c inner join Order w on w.Id = c.Id WHERE (c.valid = 1) and c.Id not in (select w.Id ...

how to convert a varchar to time in java?

hi, i'm copying data from a csv file to my database (mysql) with java. i've a time column where values can be h:mm:ss or - h:mm:ss (the - means that we surpassed a certain time delay). so i was obliged to change the column type to varchar. my problem now is i need to compare the value of records in this column, for example i need to s...

using mysqli to fetch data

i've been trying my hand at mysqli. using procedural mysql, the code displays the results fine. however, after making the switch to OOP mysqli, i tried converting the code to mysqli, and it says there are no quotes. is there anything wrong with the code? /* the database object */ private $_db; public function __constr...

MySQL joins? What are they? When to use them?

I am probably going to get slaughtered for this, and told to read the manual (which I have, but it has not helped my understanding). I working with MySQL more and more at the moment, but I cannot get my head around joins, I know there are multiple types of join, i.e Inner, Outer, Left, Right but I do not what the differences are between...

Basic SQL join query

I have a few tables: event_type (et), event (e), event_booking (eb), person (p), person_address (p) and address_country (ac) They are joined like so: et <- e <- eb -> p -> pa -> ac Every join has a one-to-one relationship, except the eb -> p link. A row in eb can have a null instead of an id from p. I want to get all bookings, regar...

How to test MySQL transactions?

I have a question about testing the queries in a transaction. I've been using MySQL transactions for quite some time now, and everytime I do this, I use something like: $doCommit = true; $error = ""; mysql_query("BEGIN"); /* repeat this part with the different queries in the transaction this often involves updating of and inserting ...

connect to mysql with c#.net

hi every body.how can i connect with MySql in app?and what software,i must install for mysql?thanks. ...

Found a weak escape function for MySql, how to exploit?

In an application I'm working on I've found a weak escape function to prevent injection. I'm trying to prove this, but I'm having trouble coming up with a simple example. The escape function works as follows (PHP example). function escape($value) { $value = str_replace("'","''",$value); $value = str_replace("\\","\\\\",$value); ...

Is it possible to condense these queries into one?

I have two tables, with one containing records referencing the other: Goal id (int) name (text) value_mask (text) GoalStatus id (int) goal (int) created (datetime) value (text) Goal.id == GoalStatus.goal What I'd like to do, is pull the latest record from the GoalStatus table, for each record in Goal. At the moment the...

mysql query issue

Let's assume I have the below table mysql> desc countrylist; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | country | varchar(32) | YES | | NULL | | +---------+-------------+------+-----+-...

Google Calendar

Hi All I have been messing about with google calendars and I think they have a good look and feel, so I MIGHT add one to my website. I just have some questions before I do. Can I set up the calendar to populate from a MYSQL datasource using PHP on my page? Does the calendar have to use an iframe? Can I force the calendar to use my St...

Using php,mysql to query two tables

My query look like this at the moment: $result = mysql_query("SELECT * FROM promo JOIN items ON promo.id = items.promo_id WHERE promo.id='$promo_id'") or die('Error : ' . mysql_error()); while($row = mysql_fetch_array($result)) { echo "<p>".$row['title']."</p>"; echo "<p>".$row['description']."</p>"; echo "<p>".$row['filen...

how do I create a bounded return from mysql?

I have a list of users, there is a column 'points' and I need to create a list of the ten people with lower points and the ten people with higher points than the user. The only trouble is I have no way of knowing at this stage what their points will be so I can't use hard values. Help? Thanks in advance :) ...

Update Query Doesnt work ?

Hello, These are my table structures tblcomment - - - - - - - - - - - - - comment_id content_id user_id picture_id comment comment_time tblpictures - - - - - - - - - - - - - content_id title picture_id I am trying to update the content_id field on pictures using this but it doesnt seem to work ? UPDATE `comments` SET com...

What is the optimal DB design for this type of data

I have a table of Users, Each USR_ID can have multiple Roles,Views,Brands and Units. So the optimal design I thought of is this: Users USR_ID USR_Username USR_Password Matrix M_ID M_USR_ID M_ROLE_ID M_VIEW_ID M_BRAND_ID M_UNIT_ID _ROLES ROLE_ID ROLE_Name ROLE_Active _VIEWS VIEW_ID VIEW_Name VIEW_Act...

Getting 'attachments' out of MySQL BLOB fields, and into the filesystem

I have a standard LAMP server, and currently there is a way to attach/upload PDF's, or JPEG's with an inventory record. I designed this system way back in 1999/2000, and it has of course grown fairly large in size. The table that holds the attachments is approaching 10GB, which has made working with the data somewhat slow (especially w...

Hibernate: Does a thread block while waiting for the database to insert a batch?

I'm trying to find the optimal batch size for some operations we perform in bulk (persisting collections). If insert is going to block a thread, I think I'm going to want to make the batch size ~ the average size of the collection we are going to persist. If that isn't the case, it might make more sense to find a smaller batch size to ...

Losing mysql result resource after first use

Hello I have the following code: (query added) $weight_table = mysql_query ("SELECT * FROM ".$prefix."weight ORDER BY wlimit DESC"); foreach ($weight as $area => $weight) { echo $weight; while ($weight_row = mysql_fetch_array($weight_table)) { print_r($weight_row); $limit = $weight_row['wlim...

What mysql engine for huge amount of data (logging)?

What mysql engine would be best suited for handling huge amount (many rows) of (small) data? I talking about logging. I'm thinking about logging whenever I do things on my page, like calling a function, calling a file and so on. A tip of how I should structure the table is also appreciated. ...

Mysql WHERE statements priority during execution

Hello, SELECT * FROM articles WHERE title LIKE '%query%' AND user_id=123 The user_id column is index. How Mysql will execute this query? I think, that LIKE have lowest priority, right? Thank you. ...