MYSQL :: Index Slows Down The SELECT

Query (select/insert/update)Slows When Table Grows
I have a mysql database where tables are very simple. An example table will look like this:

create table myTable(
token varchar(255) not null primary key,
token_count int default 1,
frequency int
) type=INNODB;

A java program populates this table. The program reads from files (in batch mode) and inserts into myTable with following logic:

(1) See if the String to be inserted is present in myTable.(Uses SELECT)
(2) If not present the insert into myTable (uses INSERT)
(3) If yes update the table after adding the current frequency (USES UPDATE)

After processing each file issue COMMIT.

This goes on well for some time - but after the Table grows over 100,000 rows the process slows down considerably. The Strings I 'm handling is very large and I'm not sure how well Mysql behaves with very large String as primary Key.

My select and update statements are all based upon primary key(token in this case) - so question of setting index doesn't arise. Even then I have set index on token field - but it has degraded the performance. Also I have set the query_cache_size to 1MB even then it is not enhancing the performance.

A sample file has almost 30,00 to 50,000 lines. Each line having a string + blank + frequency.

After processing first few files - the Java program takes large time to process a single file - and subsequently the processing time increases exponentially.(as the tabel grows in size it slows down).

Query Slows Down After Multiple Executions
I am running mysql 4.1 and am working with multiple selects and deletes on a very large innoDB table (50 million records)

basically, i am doing a Select statement on this table based on quite a few joins with other tables with a LIMIT 10 in a c# application then doing some programming logic with the Dataset I get and finally performing a delete on these 10 records. This Select is looped over and over again until all the records are deleted and nothing is found using the select. The wierd thing is that the first 100 or so times it loops it executes the SELECT statement in about 1 seconds. Then all of a sudden the query begins to take a matter of minutes per execution.

The problem does not go away even with a server restart. The only thng I have found that solves the problem is simply waiting a matter of a few hours then I will be able to execute the query in the 1 second timeframe for another 100 cycles or so.

Select Speed And Index Vs FK
I have two tables Books and UserBooks

Books:
id,int PK
value, varchar
type,tinyint

UserBooks:
id, int PK
userid int
bookid int
type int

My problem is that I want to see the most "possessed" book.
SELECT value,count(*) as cnt FROM Books,UserBooks where books.id=bookid group by Books.id order by cnt desc;
I have a Index on UserBooks on userid,bookid and type and an index on value on Books.
I created the last index because the query is very slow (300.000 books and 800.000 UserBooks rows) and I was wondering if I have to modify userid and bookid and set them as foreign key..maybe it's faster.
Otherwise I have no idea what to do. The query is very slow.

I plan to add AND SUBSTR(value,1,1)='X' (where X is every letter of the alphabet) and I think it will get even worse than now.

Use Of Index For SELECT DISTINCT
I may be wrong but i find something strange in index use when selecting distinct value. For example, let's assume we have a table like that:

a: integer
b: varchar(128)

Let's say that b has only a few distinct values but the table has MANY rows (2 000 000). The column b is indexed, with of course a VERY low cardinality.

Why optmimizer will always scan the 2 000 000 (using index on b though) to execute query SELECT DISTINCT b FROM table while it knows there are only a few distincts values from the index?

Best Index For Select With Where/order/limit
CREATE TABLE `db`.`messages` (
`unixtime` int( 10 ) NOT NULL default Ɔ',
`username` varchar( 20 ) NOT NULL default '',
`sender` varchar( 20 ) NOT NULL default '',
`content` mediumtext NOT NULL ,
KEY `username` ( `username` ) ,
KEY `unixtime` ( `unixtime` )
) TYPE = MYISAM ;
This is the kind of select that will be used a lot :

SELECT unixtime,poster,content FROM messages WHERE username='$name' ORDER BY unixtime DESC LIMIT $x,10
username has an index on it, because of the WHERE part. Unixtime has a time(); value in it wich I use to display the date, and because I also use it for ordering by date it also has an index.

I dont think this current setup is wrong, but it could be improved, and I want to know if the following things are right :

I could add an 'id' int (10) column, and set it to auto-increment and primary key and drop the key on the unixtime column. That way the messages will already be stored by date (new messages will always get a higher id value), and for mysql it will be easier to find the 10 posts that I want. I can not simply change 'unixtime' to a primary key, because on busy moments there is a real chance two users might send a message at the exact same second, resulting in a non unique value for that column.

SELECT unixtime,poster,content FROM messages WHERE username='$name' ORDER BY id DESC LIMIT $x,10
It will use the key of username to find all messages for this person, and it will then use the primary key to just select the 10 messages that are wanted. Those messages are already ordered by id (thus by time) when reading them so it will not need an extra sorting process ? unlike the ordering by unixtime wich probably requires all rows of that person to be read, and then all of them to be sorted to find the 10 that are wanted. So ordering by primary key would be faster and/or use less memory ?

I could go even further and also drop the key on username, and instead have one primary key consisting of ('username','id') , with the id still an auto-incrementing INT. This way there is only one index that has to be loaded, wich can be used for both the WHERE and ORDER BY.

The table would be like this then :

CREATE TABLE `db`.`messages2` (
`id` int( 10 ) NOT NULL AUTO_INCREMENT ,
`unixtime` int( 10 ) NOT NULL default Ɔ',
`username` varchar( 20 ) NOT NULL default '',
`sender` varchar( 20 ) NOT NULL default '',
`content` mediumtext NOT NULL ,
PRIMARY KEY ( `username` , `id` ) ,
) TYPE = MYISAM ;
I have done some testing , and used 'explain' too. With my first table it uses the username key and a filesort, on the second with the double primary key it reads a bit more rows, but its not using filesort anymore... so that should be an improvement ? I think it is, but would like some confirmation before I change it on dozen tables in the live database. And are there any other changes in index that would be even better ?

SELECT With Multiple WHERE Statments (using An Index)
I have a 35000 record database relating to geneology, about 50 fields per record, most null at any given time, almost all the fields are in TINYTEXT format.

I have a php frontend with two different search options. One that allows you to search 5 fields at once and one that allows you to search everything.

So on my homepage you can search by

surname (tinytext)
firstname (tinytext)
sex (1 char)
date (2 tinyint)
location (tinytext)
arrival port (tiny text)

On an advanced search page you can search by all 50 fields selectively.

When the SELECT is run, it basically checks which form field is filled in and if it is, then attach an AND statement to the end of the query. The initial query is "WHERE recid IS NOT NULL" because it was the only way I could figure out how to run a query where I didn't know which fields would be filled in.

This is my first foray into massive SELECT statements and I just want to know if I'm setting up the SELECT correctly and what index methods I could use to speed up my SELECTS.

Currently now a standard SELECT gets concatenated as such...

SELECT recid,surname,firstname,yy,county,port_arr,sex,seek_first,seek_surname,seek_rel from main WHERE recid IS NOT NULL AND ((soundex(firstname) = soundex('$firstname')) OR (firstname LIKE '%$firstname%')) AND (age = '$age')

This varies a great deal as to which fields are filled in.

I just want to write the fastest selects i can. Any kind soul willing to offer a hint or a book I should be reading?

Select With Partial Length Index
I have a table, table1, with the field 'areacodeplustelephone'. This field
is of length 10. I created an index called 'areacode' that only uses the
first three characters.

Can I use this index in my select statement?
I tried this: select name from table1 where areacodeplustelephone =
330' -- no results

I tried this: select name from table1 where left(areacodeplustelephone,3) =
'330' -- got the results but slow...id didn't use the index

I'm I going about this the wrong way? Is there a better way than using the
"left" function?

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 ....

It Doesnt Use My Index!
`player_1` mediumint(7) unsigned NOT NULL default Ɔ'
`player_2` mediumint(7) unsigned NOT NULL default Ɔ'
reffering to users' id-number in another table.

I have two indexes:

KEY `one` (`player_1`),
KEY `two` (`player_2`)
The cardinality of them is 6000 and 7000. My primary-key has cardinality 150 000.

When i run this query:

EXPLAIN SELECT *
FROM `gamebase`
WHERE player_1 =1
It uses the "one"-key (doh!).

But now, that I run my next query (using OR), it tells me that one and two are possible keys, but it uses neither, but loops through my entire table "Using where".

EXPLAIN SELECT *
FROM `gamebase`
WHERE player_1 =1
OR player_2 =1

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?

Generated Index
We have a small database running on Win XP. We use Eclipse for development of our java app that uses this db.

When I insert a new record into a table with one existing entry, the key that MySQL generates for it is a huge number and causes errors in subsequent processing. The table is created by this:

CREATE TABLE scope
(
scope_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
activitygroup_id INT NOT NULL UNIQUE,
FOREIGN KEY (activitygroup_id) REFERENCES activitygroup(activitygroup_id) ON UPDATE CASCADE ON DELETE CASCADE
);

We have several other similar tables, but this is the only one that causes me this problem. We tried using MySQL Query Browser to change the 'index kind' from INDEX to UNIQUE and that solved the problem for my co-worker. It didn't do anything for me, though, so I suspect it's a red herring.

Can anyone suggest what I might try? I'm a completely new user. I started to look at the MySQL documentation and my eyes glazed over, so I'm quite ignorant of how this all works.