MYSQL :: Equality Testing With NULLs?

Equality Comparison Of Char
Is there any difference in speed between an equality comparison of char (or varchar) column values with a constant, and comparison of int column values with a constant?

i.e. would this:

select * from table where col1 = 'blah';

be much slower or faster than

select * from table where col1 = 6;

this?
Obviously the difference is probably miniscule, but this is a query I am planning on running in a lot of loops so it could be important

NULLS 2
if I have a table for:

PHONE_NUMBERS

that can be associated with FKeys to one or more COMPANY_ID and/or a PERSON_ID how would you construct the columns/relationships?

Assuming a PHONE_NUMBER_ID as PK then a n:m relationship to both the COMPANY and the PERSON tables??? (i.e. a new table with a compound PK of phone_number_id plus company_id plus person_id) but then I'd have to allow either the company_id or the person_id to be null wouldn't I?

NULLS
if you had a table with columns:

Prefix_ID ---> data for say 75% of rows normalized to Mr/Mrs./Ms
FirstName ---> data for 50% of rows
MiddleName ---> data for 25% of rows
LastName ---> data for 100% of rows
Suffix_ID ---> data for 5% of rows normalized to Jr./Sr./III

would you:

1.) make suffix_ID NULL-able ?

2.) make any other column NULL-able?

3.) make Suffix_ID default to 1 where Suffix_ID 1 = ''?

4.) make FirstName and/or MiddleName default to '' ?

or other alternatives/suggestions for best practices?

Concatenating Nulls
I would like to concatenate two columns where one is potentially null. The result I get now is if one is null, the concatenated value is null. Here's what I would like:

Column1 = '123', Column2 = null, return '123' (not null)
Column1 = null, Column2 = 'abc', return 'abc' (not null)
Column1 = '123', Column2 = 'abc', return '123abc'

The statement that isn't working is something like:

SELECT CONCAT(Column1,Column2) FROM Table WHERE id=12

Joins And Nulls
I have three tables containing information about companies. The first lists companies, each with an id. The second defines what information (attributes) can be held about each company and format it takes (eg address, postcode, owner, etc). The third table lists the specific values of each attribute for each company.

Each attribute value for each company has its own line.

Now, I am trying to select data from this group of tables. The problem is that not every company has complete information. Some, for example, don't have a postcode. But where they do, I'd like to display that. Which brings me to this SQL: Code:

NOT NULLs With Default
I'm cleaning up a database but I see this all over the MySQL world. I understand that having defaults speeds things up. But what I don't understand is why someone would make a field NOT NULL and then put a null default in it? This makes no sense to me at all. Is this the fault of using phpMyAdmin to create tables?

Here's a sample:
CREATE TABLE `listnames` (
`id` int(11) NOT NULL auto_increment,
`list_name` char(30) NOT NULL default '',
`display_name` char(100) NOT NULL default '',
`active` char(3) NOT NULL default '',
`last_modified` timestamp(14) NOT NULL,
UNIQUE KEY `id` (`id`)
)

Ok, so you don't want NULLs in your data but there is no default. It's very likely that the data will be wildly differently. So setting a default makes no sense -- or does it?
I don't want to drop the default '' if it makes sense & I'm just not getting it.

Finding NULLS
I am querying with the following hoping NULL and not NULL:

SELECT *
FROM pet
WHERE sex='NULL';

returns empty set, but there should be one record.

SELECT *
FROM pet
WHERE death<>'NULL';

also returns empty set when there should be one record.

Count Of NULL And NOT NULLS
How would I write a query to count the number of instances where a the
PREP field is NULL and when it is not null. I know how to do it
seperately, can I get the numbers in the same query. I want something
like this:

Project #PREP_NOTNULL #PREP_NULL

Ignoring NULLs In Indexes
Access has a nice feature that allows creation of indexes WITH IGNORE NULL which means that records with null values are ignored in the index, so the index will be
shorter and faster to search. Is there any equivalent for this in MySQL?

SQL Select Statement Returning NULLS
when I run this SQL qry:

SELECT
DATE_FORMAT(recDate, '%Y-%m-%d') AS a_date,
recTitle, recBody,
recID
FROM j_plug
ORDER by recID DESC
LIMIT 15;
The 'recTitle' column often returns a NULL. Is there any way I can change the SQL statement to return an actual value if there is a NULL, such as 'NOTHING'.

This is probably a silly request - obviously if it is a NULL, then that should be that. Trouble is that I am having a problem with my ASP code when the value is NULL, and it'd be useful if it returned an actual value for NULL records.

Unexpected NULLs When Using 'left Join'
I'm trying to select all records from a table, plus the appropriate values from other tables to which my first table has pointers. I quickly discovered the benefits of LEFT JOIN over WHERE clauses but then found that I was getting more NULLs than expected. For example: Code:

Unique Index With Nulls Where Is It Stored
The memory space defined by DataMemory is also used to store ordered indexes, which use about 10 bytes per record. Each table row is represented in the ordered index. A common error among users is to assume that all indexes are stored in the memory allocated by IndexMemory, but this is not the case:

Only primary key and unique hash indexes use this memory; ordered indexes use the memory allocated by DataMemory. However, creating a primary key or unique hash index also creates an ordered index on the same keys, unless you specify USING HASH in the index creation statement. This can be verified by running ndb_desc -d db_name table_name in the management client. if i issue a :

create unique index idx_b on tablename(b);

where b can contain nulls is this index stored in index memory and in data memory or just data memory?

NULLs And Empty Strings, Treating Them Equally?
I've built a database class in PHP which, when given an empty string for a field value, converts it to NULL, simply because although their technically not the same thing, as far as useful data goes, they are equally useless.

I want to use the NOT NULL flag on fields to dictate which fields should have data, and which shouldn't, and really, an empty string is no data.

I was just wondering what other peoples opinion was on this, and weather or not it is a reasonable restriction to enforce, I cant think of any situation where data is required for a field (NOT NULL), and an empty string would be a reasonable value.

Testing MySQL
I'm trying to test MySQL (i've already tested php and it works, so I'm not real sure I need this step, but I'm sure I need to know how to do it reagardless).

I test it using localhost as the hostname and it works except that this is what the page looks like. ....

Testing For Bit Set In Int Field
Say I have a column of type int(4) and it represents a bitmapped pattern, so bit pos 1 means "apples", 2 means "oranges", 3 means pears etc. The value 3 is "apples & oranges". How would I construct a query such that I could return these?

For example, if I was searching for oranges and the bitmap field was called type in table fruit the logic would be

SELECT FROM fruit WHERE (type & 0x02) > 0;

All my tests so far just return all values.

DB Testing Software
Are there any available tools for testing mysql db performance?
What do you guys typically use for testing your db's performance and data integrity?

MySQL Testing
I just recently installed MySQL and am also running an APACHE 2.0 server on my computer (Windows XP machine). I followed the instructions in the help document on installing MySQL and I believe it is installed properly, no errors occured, it was pretty straight forward.
However, when it comes to testing, the documentations on give commands using the "Windows Command Line Client" and not the MySQL Command Line Client.
I installed MySQL as a service, that turns on and off with Windows.

How To Universally Act On All Cells In A Table, Turning Blanks To NULLS
i should be able to do this, but it's actually been a pain for a while. how can i turn all the blanks in a table to NULLS?

LOAD DATA INFILE, Updates All Other Fields With Nulls.
I am using the following command to update three fields ONLY in the destination table. The infile that I am using was created using:
..........................................................
select code, desc2 , qty_on_hand from plu_tmp
into outfile "c:mp.txt"

..........................................................
LOAD DATA INFILE "c:mp.txt"
REPLACE INTO TABLE `plu` (code, desc2 , qty_on_hand)

I expect to update only the three fields, without touching the other fields.
What happens is that the specified fields are updated, and all the rest of the fields are reset to nulls as they have not been specified.

Is there any way I can use the load inifile to insert / Update just the
specified fields.

How Do I Set Up My System As A Testing Server
I work on a Windows XP Pro. Platform and I am having trouble setting my system up as a Testing Server for PHP pages. I want to be able to test scripts and build my database. I tried downloading and installing MYSQL and PHP but it still does not work. Is there some kind of Configuration I need to do in the PHP.ini file?

Load Testing Tools
We are developing an application using PHP and MySQL 4.0.16, that we need to stress test. Can someone please point me to some testing tools we can use?

Load Testing Tools
We are developing an application using PHP and MySQL 4.0.16, that we need to
stress test. Can someone please point me to some testing tools we can use?

Testing SSL Conections W/ MYSQL
I recently installed all MySQL 4.1.1 including max RPMS. During the
installation i used "--nodeps" on MAX package and at Shared-compat
cause they were requiring the libssl.0.9.6 and libcrypto.0.9.6. I am
using a RH 9 and these files are installed on my system.

The result for mysql query :

show variables like 'have_openssl';

is

have_openssl | YES

I created a user using the grant command with 'require ssl' like
this:
[color=blue]
> grant insert,select on MyDatabase.* to myssluser@myhost require ssl;[/color]
Query OK

I can connect using this username and CLIENT_SSL param (isn't this
param just for php 4.3 or higher?):

mysql_connect($dbserver, "myssluser", $dbpass, CLIENT_SSL);

It runs on my php 4.2.2 compiled with openssl 0.9.7, but i need to

TEST if this is a real ssl connection.

Testing If Mysql Is Running
how can i test if mysql is running on my machine?

Load Testing MySQL
I know this is quite a bit off topic, but i was wondering if anyone else
out there had any experience with Segue's Silk Performer load testing
tools. More specifically, i am trying to compile a test script to allow
me to perform some transactional database testing to load test a MySQL
4.0.13 database server running behind some apache webservers. I have
obtained an ODBC driver from MySQL and have some specific queries i
would like to use in the test. I am have used SP to load test the
webservers specifically, but i am not 100% sure how to run this against
a database server now.

If anyone has any experience they can share, please feel free to email
me off list.

Testing On Update & Delete Cascade
When I added indexes and foreign keys to my tables I also inserted ON UPDATE CASCADE and ON DELETE CASCADE.
However, when I tried testing that this works by entering:-
select * from address, name where address.name_ID=name.ID;
It says empty set. What am I doing wrong?

Testing Speed And Query Cache
I'm trying to test performance on some queries with a million of registers on my table, using various keys.

The problem is that the first time i run the query, it takes about 4/5 seconds, but after that it takes about 0.02 seconds. I know that query cache can be flushed with reset query cache, flush query cache, flush tables also modifing some caps at the sql query, but i'll never take 4/5 seconds again.

for example:
select * from table where id=2 order by index_field desc; 4/5 seconds.
select * from table where id=2 order by index_field desc; 0.02 seconds.

select * from table where id=3 order by index_field desc; 4/5 seconds.
select * from table where id=3 order by index_field desc; 0.02 seconds.

etc, etc...

Testing On Update & Delete Cascade
When I added indexes and foreign keys to my tables I also inserted ON UPDATE CASCADE and ON DELETE CASCADE.

However, when I tried testing that this works by entering:-

select * from address, name where address.name_ID=name.ID;
It says empty set. What am I doing wrong?

Testing Parameters Within A SELECT Statement
I am building a SELECT statement where I will be passed search parameters depending on what user has entered. Each parameter may have a value or maybe NULL.

What construct (CASE, IF, etc) can I use to build my WHERE clause so that the passed value is included or if NULL, all rows are returned.

SELECT *
FROM table
WHERE col_a = val_a (or if VAL is NULL then all rows)
AND col_b = val_b (or if VAL is NULL then all rows)
AND col_c = val_c (or if VAL is NULL then all rows)
etc ...

Bogus Datasets For Database Testing
I'm teaching myself mysql and wonder if there are any sources of
inane, complex, bogus data that I can download and insert into a
table(s.) I have created some very small sets to practice joins and
whatnot but they are not very big or complex, or exciting.

compressed tar of text strings (no blobs) could have plenty of data
but still be a manageable download. It would also be a nice test of my
machine and tuning skills.

ERROR 1045 When Testing In The Control Center
In the MySQL Control Center 0.9.4-beta, I right click the top level server name and choose the Edit option. Then I have entered a User Name and a Password in the General TAB. I then mark the option "Promt for Password" and hit the "Test" button.

I get a small window where I should enter the password for User name@Host name. I enter the password and hit the OK button. Now I get the error message "ERROR 1045; Access denied for user; 'User name@Host name' (Using password: YES)"

When looking in the Help menu, the Contents - F1 is not activated, so I can't get any online help.

I'm just wondering why the test for the password failed and why there are no online help in the consol?

Testing The Database? Backing Up, General Checks
I want to know what else I can be doing to check the stability of our database. Currently what I do every evening is download the complete database through PHPMyAdmin on our dedicated server, once downloaded I start up the Wamp Server and import the database locally through PHPMyAdmin on my own machine.

The next thing I do is run the Check Tables command on all the tables to see if everything is where it is. It always comes back as OK on each table so I presume everything is ok?

Is there anything else I should be doing, or is what I am doing correct?

Every so often we delete auctions to free up space in the database. Once this is done we have overhead in the tables. Sometimes this can be 2-10mb, so what I do then is run the optimise command on the tables, again am I correct in doing this?

Finally our database is now over 40mb, to me this massive as the largest database I have worked on before has been 4mb, please dont laugh.

What sort of database sizes have you seen in MySql or are you running at present? What is MySql like in the long run stability wise. If looked after can we get many years of good service from it.

Testing Access To MySQL-DB In Windows-Network
Background: I am writing in C# a program, which reads data out of a MySQL-DB. When the DB is on my local developer PC everything is fine. But when trying to access the MySQL-DB on a server in our Windowsnetwork, I receive the message: "Host '**' is not allowed to connect to this MySQL server".

Testing For An Empty Set Returned By A Nested Select Statement
For those of you following the "While Loop Within Select" thread... I've been restructuring my database to incorporate the changes. Now I've got a table of skipped stops rather than a table of stops (I need it that way for various reasons) and I need to return the schedule to the user as long as the user's stop isn't in the list of skipped stops. SO say something like:

SELECT
yada yada yada
FROM
schedule AS 's1'
WHERE
(
SELECT
skippedstop_id
FROM
skippedstops
WHERE
schedule_id=s1.schedule_id
AND
location_id=$origin
) == "EMPTY SET"

Only I have no idea how to actually test for the empty set from the nested select...

Transferring Tables From Testing Server To Online Server
I need to copy my tables from a testing server on a local area network to my hosting companies online server. Is this something I can use MySQL Query Browser to do, and if so how?

NULLS (was "Is This Normal?")
to have empty cells in the results?
like...i have one column called 'specs' but only a few rows in the result have actually a value in it, the rest is NULL
i know beforehand that the result will be like this most of the time...is it bad?

Equality Testing With NULLs?

When I do "SELECT count(1) FROM table_name WHERE 0 <> 2", I get the total number of rows in the table. When I do "SELECT count(1) FROM table_name WHERE NULL <> 2", I get 0. Shouldn't I the same answer as I did with WHERE 0 <> 2?