MYSQL :: It Doesnt Use My Index!

WHERE Doesnt Equal
I am trying to select something my DB where a clause does not equal something but for some reason I cant seem to make it work.

Here is what I have tried.

PHP Code:

SELECT * FROM golf_groups WHERE name!="something"

but this doesnt seem to work. i had done something like this before and thought that I had done it this way, but since this does not work,

Its Returning A Value That Doesnt Exist
Okay here is the deal. I am running a query to find out if there is an ID in my table that has a case number that matches the one being submitted. If there isnt once its should step into an if statement that would submit a number of records. Otherwise it should jump to the else statement.

I the problem comes from: the query keeps returning "Record id#2" but there is no record matching the case number being submited from the form.

if( isset($_POST['Submit'])){ //Begin If statement logic
$resultcheck = mysql_query("SELECT * FROM submissions WHERE wfmcase = " . $_POST['wfmc']) or die(mysql_error()); //Query string to test if wfmcase was already submitted
echo "Sucess, form varibles have been passed.<br />";
echo "Resultcheck query results: $resultcheck <br />";

if ($resultcheck == ""){ //Checks to see if the query was empty.
echo "There are no duplicate case escalations pending. <br />";

I added the echo statement so that i could see how far it was getting through the script. It dies after printing the varible $resultcheck which gives "Record id#2". It never enters the 2nd if statement because for what ever reason the query has the "record #id2" in it... What is causing this? I have verfied that there is no case that matches the one coming from the form and i get the same error.

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list.

GROUP BY Doesnt Work
i have a table that contains the folowing fields:

some_table:

company_number
something_number
price
date_price_established

for a certain company i need to get all the dates that prices were established. and i dont want any date_price_established to repeat themselfs.

for example, for the following database:

1,1,15.5,1.1.2006
1,1,16,1.1.2006
1,1,17.5,1.1.2006
1,1,16,3.1.2006

the results will be : 1.1.2006, 3.1.2006.

this is what i've tried:

SELECT date_price_established
FROM myDb.some_table
WHERE company_number = 1
GROUP BY date_price_established";

Where Clause Doesnt Work
im creating a website with a mysql database. ive jus got started. im havin a few problems
i have a product table and i execute a simple query but it doesnt seem to give me any results: heres the query:

Select * from product where ProductName = 'Age of Empires III';

the above query doesnt work but this 1 does
Select * from product where ProductName LIKE 'Age%';

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table d=
oesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo=
cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN=
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B=
Y l.location ASC

so when shotlistID is selected all the keys from the locations_join joined =
to the shotlistID would be remove from the locations list please help, i'm =
trying to do this in one query saving from getting all the keys into an arr=
ay then checking if the values arent in the array when generating the list.

Domain Name Now Doesnt Work
I have a software script I had installed on my server that works with a mysql database. It was working fine as far as pulling information from the database under my old domain name, however, I changed to a new domain name as primary on my server and now the database does not pull anything into the template.

I know this is probably simple but not sure how to fix this.

Function Doesnt Work
i tried write simple function:

CREATE FUNCTION `getLastNumber`(some_guy INT(50), date DATE) RETURNS float
BEGIN
DECLARE a FLOAT;

SET a = NULL;

SELECT number INTO a FROM numbers p WHERE p.number_owner = some_guy AND p.date_created <= date ORDER BY p.date DESC LIMIT 1;

RETURN a;
END

that would return last inserted number into table for some person. When some_guy has number in table, then is everything ok and it returns last number, but when some_guy does not have row in table it returns: Query OK, -1 rows affected. Pls why? and how to fix it?

Mysqldump Doesnt Work
i try to insert the followind data to mysql database
mysqlimport -u root -p anaktisi2 < phpbb_db_backup.sql

But this doesnt insert anything in the db.

Why Doesnt Limit Work Fully
Why is it, when you have a query, and you limit something, it doesntlimit the records searched? such as, i have a query that searches for "joe" and puts it in ASC order and limit 5, itll go through all joe's, is limit only good for displaying records, such as only 5 records show, ? if that made any sense :)

Full Text Searsh Doesnt Work
I am trying to do a Full text search, but I have two problems.

1. If I enter a word that is found under multiple entries, I get no return. But it i search a word tha's only under one enetry, then it works.

2. This query works:
PHP

SELECT * FROM `site` WHERE MATCH (site_name,keywords) AGAINST ('mice') AND `approved`=Ƈ'

, but when I add WITH QUERY EXPANSION, it doesnt work.

PHP

SELECT * FROM `site` WHERE MATCH (site_name,keywords) AGAINST ('mice' WITH QUERY EXPANSION) AND `approved`=Ƈ'

I get this error:

MySQL said:

#1064 - 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 'WITH QUERY EXPANSION ) AND `approved` = Ƈ'
LIMIT 0, 30' at

Full Text Index Before Or After Non-clustered Index
In mySQL should any required non-clusterd indexes be built before or
after a full-text index? Either on the same column or different
columns...

Index, Then Query On Only Part Of The Index
Lets say we have a fulltext index on...

column 1, column 2, and column 3

If we do a select statement matching only on column 2 and column 3 will the index still be used effectively?

Do we then need a second index only on column 2, and column 3?

Would it be smart to simply create indexes on all of the following?
column 1
column 2
column 3
column 1, 2, 3
column 2, 3

?

Howto Add Another Index To A Default Index
I have a index rang of 1-5 set as default. I am now trying to add an index 6, i know it sounds easier enough but i cannot come right. Let me past the code segment below to get a better idea of what is happening: Code:

Performance Of UNIQUE Index Compared To "normal" INDEX
I have a table with several columns. one column "myColumn" of this table has a UNIQUE index on it. i want to use this column in many many search queries.

does it make sense - in terms of performance - to add an additional INDEX to "myColumn"? or does an UNIQUE index already "include" a "normal" INDEX?

What Should I Index In This?
If this is my query and I'm calculating distance between users what should I index in the database? This query works really fast in my database of 10,000 users when I have state='xx' in the where clause. But if I don't it goes crazy and takes minutes! I'd love to sort by distance but that takes too long as well. Any ideas on what to index?

SELECT SQL_CALC_FOUND_ROWS users_info.username,users_info.id ,CONCAT(zipcodes.city,', ', zipcodes.state) AS user_area ,DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthdate)), '%Y')+0 AS user_age ,user_greeting.greeting , (3958 * 3.1415926 * sqrt((zipcodes.latitude - -111.978898) * (zipcodes.latitude - -111.978898) + cos(zipcodes.latitude / 57.29578) * cos(-111.978898 / 57.29578) * (zipcodes.longitude - +40.606125) * (zipcodes.longitude - +40.606125)) / 180) as distance
FROM users_info
LEFT JOIN user_greeting ON users_info.id = user_greeting.user_id
INNER JOIN zipcodes ON users_info.zip = zipcodes.zipcode
WHERE users_info.birthdate<&#55614;&#57139;-02-16 06:34:03' AND users_info.birthdate>&#55614;&#57130;-02-18 06:34:04' AND users_info.state='CA' AND users_info.sex = Ƈ'
HAVING distance<ཕ'
LIMIT 0,10

Should I Use Index?
I have table Files(id,name,type) . Type is INT, the possible values are(1,2,3,4,5). Often i execute query which gives me files of given type e.g.:
SELECT id,name FROM files where type = 1

Why LIKE Not Using INDEX?
I have a table with an intger column called ID. I have
an index on it.

Now I want to get a result with all the rows whos ID
values start with lets say 12....i.e. I want all the
IDs with data -

12
123
1234
...........
select * from tablename where id like '123%'

It is returning me correct results...but the query
does not seem to be using the INDEX?

My question is ... does MySQL not uses INDEX when we
execute a LIKE command on a NUMERIC data....
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw

Index
I read that it's preferable to shorten the length of an index if it's based on a varchar. But if I shorten a column defined as:

name varchar(40) with an index of:

index ix_name (name(10))

and then I try to do an insert into the table a "name" longer than 10 (but less than 40) the insert will fail with the error message "data too long for column name in row XXX".

what am i doing wrong? (mysql 5.1)

Index
Not sure how indexes work with ORDER BY and GROUP BY. What index should I create which will be used on

SELECT '' As ID, `Source Presidency` As Presidency, `Source Name` As Name,
MIN(`Source Year`) As `Start Date`, MAX(`Source Year`) As `End Date` FROM
burialdeathfull GROUP BY `Source Presidency`, `Source Name` ORDER BY `Source
Presidency`, `Start Date`

Everything I ttry doesn't work.

2 Index
i've been looking at some of my old databases with a view to optimising them as i'm totally self-taught and never realised loads of stuff like indexing when i created the databases! i'm using MySQL 3.23.58, all tables using MyISAM.

on one of my tables (orders) i have built an index on the customer ID field but when i view it in phpMyAdmin this index has the same cardinality as the PRIMARY index (i.e. order number). now i know for sure that some customers have ordered more than once, so shouldn't the cardinality of the customer INDEX be lower than the PRIMARY INDEX? the customer ID field is type MEDIUMINT(8).

on another database, in another table, i have 4 text columns for varying degrees of scientific calculations (species, genus etc). i have built a FULLTEXT index across the 4 columns as i want non-scientific people (who don't understand the difference between genus, species etc) to be able to query the database. now i don't seem to be able to add any other INDEXes on this table. i want to index the genus column by it's first letter so i can browse the table by letter. this works fine without the FULLTEXT INDEX but not with. is this right that a FULLTEXT INDEX can be the only INDEX (other than the PRIMARY) on a table.

Index To Hit First
I have a multi-million row table with three indexes in MySQL-5.0.15.
These indexes have the following number of distinct values:

date 415

block 100000

scan 45

If I'm doing a query on this table in what order should I hit these
indexes? The largest first, to get rid of the most possibilities right
away? Or the smallest first, because it has the fewest number of rows
to scan to make the cut? At some level both seem to make sense, but I'm
doing smallest to largest. Is this the most efficient? Or is this a
case of just letting the query optimizer choose? I don't yet have a
compound index on these fields.

Index Is Not Used :-(
It is a simple query and my index is not used :-(

Index is "zadnj"e and I indexed "zadnjidatum, zadnjiuporabnik" together.

Query is

EXPLAIN SELECT r.ID, r.imerecepta, r.zadnjidatum, r.zadnjiuporabnik, r.stmnenj, o.ime, o.uporabnik
FROM recepti r
INNER JOIN obiskovalci o ON r.zadnjiuporabnik = o.ID
ORDER BY r.zadnjidatum, r.zadnjiuporabnik

Explain says:

id select_type table type possible_keys key key_len ref rows Extra
1SIMPLErALLzadnjeNULLNULLNULL6788Using filesort
1SIMPLEoeq_refPRIMARYPRIMARY4r.zadnjiuporabnik1

What am I doing wrong? I used another similar query and I didn't get using filesort and not using index.

Using Index
I had a 1.6 Gb table (innodb) with 12 indexes, which I later reduced into a 250Mb table with 2 indexes. The problem is that the index seems to be bigger in size than the table (270Mb). What am I doing wrong? How can I make the index smaller?

Why Don't Use Index
I have a table Test with three fields:
id int(11) not null auto_increment;
name varchar(15) not null;
age tinyint(4) not null.

[1]select * from Test where id like '%1';
[2]select * from Test where id like '1%';
It's said that query[1] cannot use index, but query[2] can do.
But with my tests, both of them didn't use index.

My tests were very easy, I used "log_queries_not_using_indexes" option.
The relevant statements in my.ini as follows,
log-slow-queries=slow-query.log
long_query_time=10
log_queries_not_using_indexes

I just had a little data, so both queries weren't "long time query".

Index
what is index in mysql Action next to PK.

WHERE..IN Not Using Index
Here is the query:

SELECT `vkey`, `data` FROM `registry` WHERE `vkey` IN('prioritycache','statuscache','staffcache','departmentcache','slaplancache','slaschedulecache',
'escalationrulecache','attachmenttypescache','ticketcountcache','ticketgridcache','ticketviewcache',
'queuecache','instaalertcache','ticketlabelcache','labelcountcache','gridcache','groupsettingcache',
'commentcache','staffassigncache','groupassigncache','tgroupassigncache','settingscache','tgroupcache','languagecache');

An INDEX exists on the `vkey` column. I've already OPTIMIZE, and ANALYZE this table.

But when I run the query with EXPLAIN, no indexs are used.

There are 58 rows in this table.

If I reduce the number of options in the IN portion down to like, 5 or 6, the index is used. But if you increase past 5 or 6, the INDEX is no longer used.

INDEX Became KEY
i've created table manually and set INDEX on the one of my columns, then i exported my table with phpmyadmin and the INDEX became KEY in the output. are they similar?

Index
How to know if a column fileds are distinct or not ?

Index
I have a session table, where I stored user sessions in the database in a memory table. My question is, performance wise, would it be smarter to delete inactive sessions every 60 seconds via client http requests, or would it be wiser to select active sessions via a select that uses a where clause and searches a timestamp index column for active sessions, and then a cron job is scheduled to delete every 30 minutes.
Which of these would be the better choice? On each page request I am going to have to select the number of sessions that exist and a second query that displays the active users. So if I go with my second choice, I will have to include a WHERE clause that scans the index on both those queries. But if I am issuing a delete every 60 seconds, then I won't have to do that, but then the delete is taking place every 60 seconds on a client request.

Getting The Index
I've got a table with an auto-incrementing index column (named "ID") which is also the primary key. After inserting a new record, is there an efficient way to get the index at which it was inserted?
Presumably I could do "select max(id) from tablename", or "select ID from tablename order by ID desc limit 1". (Which one would be faster, BTW?) But that's assuming that no other records are inserted between the insert and the query.
Is there a common method of doing this?

Index Help
I deleted a fulltext index on one of my mysql tables, which then broke my php frontend. I recreated the index, but the app is still broken.
How can making a change to a secondary index, or in this case a fulltext index, adversly affect my application?

Index
Do I have set index (using KEY) on a field which is already indexed literly (using FULLTEXT INDEX)?

Index 7
The following is part of my script that does not work, the first line is what i think is causing the problem. going by the error returned.
i have had this before and looked at my other script to work it out again, but can not see anything wrong

$res = mysql_query("SELECT * FROM list WHERE email='$findstring' LIMIT 1") or die(mysql_error());

$name = mysql_result($res, 0, "name") or die(mysql_error());

An Index
I've a mysql database running with a lot of documentation files.
Now I wonder how it is possible to generate an index file (in plain text) of
this files sorted by description for publishing on my website.

Search Index
I have text stored in a database that I'd like to be searchable. However, searching through long strings and blobs seem like a very inefficient way of doing things.

In MS Server there's an index server that will take all strings and create indexes out of words. This can significantly speed up text searches.

Is there such a feature or tool for MySQL?

Delete And Re-index SQL
Out of interest -If I delete records from within the db are the records stored in a contiguous fashion or are there "gaps" in the table ?

How do I go about deleting records from within a db and then RE-INDEX so that the records have new index id numbers?

Index Vs Primary Key
can you pls explain me each with diffrenses ?

No Index Defined!
I am working on editing of articles.
Whatever the user enters to edit a particular article then that will be saved wow table(actual table), the already existing data goes to wow_history

PHP

$add_history = "INSERT INTO wow_history SELECT * FROM wow WHERE id = $id"; ";
mysql_query($add_history);

Before updating the table with new edits

so i have wow and wow_history table identical except that in wow_history the id is not auto incremented (if auto incremented then ONLY THE ORIGINAL data copied stored in wow_history, but rest of the successive edits are lost)

But now it says in wow_history that index not defined! So what should i do? Should i leave the wow_history without an index?

The table i use is

CREATE TABLE `wow_history` (
`id` int(11) unsigned default NULL,
`contributed_by` text,
`title` tinytext,
`content` text,
`date` bigint(20) NOT NULL default Ɔ',
`mood` int(2) NOT NULL default Ɔ',
`tags` text,
`trusted` tinyint(1) NOT NULL default Ɔ',
`modified` tinyint(1) NOT NULL default Ɔ',
`ip` varchar(80) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I tried making uid (another field as autoincremented index but then data of wow is not copied to wow_history). Can i make timestamp as index?
What are the drawbacks if there is no index?

Re-index Tables
How can I reset my tables so that the auto incremented primary key is reset from the beginning. I.e. I have been developing a db and have deleted/added updated data as I have gone along and my primary keys have holes in the index numbers. Is there a one shot command that resets all the values starting from one up to the current number of records in the table?

Index On A Primary Key?
I am trying to optimize a query that is running slow. The query uses a join:

Quote:

INNER JOIN table1 ON table1.field1 = table2.field2
AND table1.field3 = 'ok'

However, when running EXPLAIN this returns

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table1 range PRIMARY,field3 field3 10 NULL 30015 Using where; Using temporary; Using filesort
I have an index on table1.field3 already, however i cannot add an index on field1,field3 in table1 as field1 is a primary key (i understand indexing a primary key is bad practise)?! How do i get around this, as i understand mysql only uses 1 index per table per query?

Primary Key Or Index ?
I have a table contains 80.000 rows approx and I set (id) field to be primary key and index by using phpmyadmin.

my question is that right ? or it must be primary key or index not both ....

Index Selectivity
I read that if your index selectivity is over 25% (thereabouts) mysql prefers a fulltable scan (it will be faster)

I have the following table (simplified)

id
visibility (0, 1)
adult (0, 1)

I search only on these 2 queries:

WHERE visibility = 1 (AND .. other parts of query not related to these 2 columns)

WHERE visibility = 1 AND adult = 0 (AND ...)

Should I have an index on both these columns?

Visibility = 1 in 85% of the cases
Adult = 0 in 50-80% of the cases

Index & Unique Key
I created a table with two columns 'user_id' and 'name', and defined the combination of the two columns as UNIQUE and defined an INDEX on each column.
Why is the following error generated: UNIQUE and INDEX keys should not both be set for column `user_id`?

Index Question
I am seeing some weirdness on my DB. I was working on a project 3-4 months ago, and tweaked a query and indexes to work pretty fast (subsecond). Now I come back to it, and the query stalls...There are 6k more records in it than before (for a total of 13k). Here's a problem. My query is like this

CODESELECT FROM t1,t2,t3 WHERE (...) AND t1.my_id NOT IN (SELECT my_id from t1 WHERE (...) )

Persistent Index?
I have a pretty sizeable table, nothing massive but big enough to warrant an index being created on what would be the "foreign key".

The thing is that each day my program runs and it rebuilds the index and then drops it when it is completed, a process which takes about a minute, and I was wondering if this is being wasteful, and if in fact the index can just remain in place all the time? Will MySQL maintain the index as records are added/removed from the table? Or do I have to re-create the index before processing every day?

Index Usage
I have two tables, one is Student, which has studentid, personid, grade, fees as its columns. The other one is Person, which has personid, firstname, lastname, address as its columns. When given a studentid, I need to get the student' info, such as
lastname from the db tables.

I can do it with two queries as the following:

1. mysql> select personid FROM Student where studentid=1;
2. mysql> select lastname FROM Person where personid="the id I got above";

Or I can do it with one query:

3. mysql> select p.lastname from Student s, Person p where s.studentid=1 AND s.personid=p.personid

Both works. However, I can't tell which one is actually faster, since MySQL shows me "1 row in set (0.00 sec)" for all these three queries. Both studentid and personid are primary keys, so they are automatically indexed.

Will both indexes be used in query number 3? I saw people said that

MySQL could use in a query only one index...Does it mean only one index will be used in query number 3? I am wondering which one will give better performance.

Primary And Index
Right now I have a member mysql database set up with a php login page. I am using MyPHPAdmin to do this.

A member has to log in using their User_id number ( 4, 5 ,6 etc)

I would like to make it so that they can log in using their email instead.

1-Do I edit the INDEX so that I make the primary the Email field?
2- If I do this does this mean I have to change anything in the PHP script?
3-Do I have to change anything else ,anywhere else to make this take effect?