MYSQL :: Search Index

Search Engine, Fulltext Index
I'm going to develop a search engine that will crawl a number of sites. The database will be the latest stable release of MySQL. I've been reading everything I could get a hold of about MySQL fulltext searches, and it seems to be really sweet. I'd really like to use the boolean mode, but what I've read, the relevance score you get from boolean mode isn't as accurate as the relevance score in "normal" mode. I read on MySQL's website, that you could use boolean mode for the search, and "normal" mode for the relevance score, like this:
SELECT
*,
MATCH(post_content, post_title) AGAINST('string') AS `score`
FROM
posts
MATCH
(post_content, post_title)
AGAINST
('string' IN BOOLEAN MODE)
ORDER BY
score
Does this really work? I mean, the "normal" mode disregards posts where the search terms is 50% or more of the post. So if I have a post that contains "string string text", and execute the query above, do I get 0 as score?

I know you shouldn't use fulltext if the posts only contain such small texts, but that's a part of my problem. The search engine should give extra weight to headings and bold text etc, and these aren't always more than a few words. So the problem is that the normal text on the webpages will be long enough to use fulltext index on, but the headings won't. So how would I solve that?

First, I thought along the lines of a table like this:
tbl_texts
t_id - primary key
t_fk_pid - foreign key to the webpage the text is on
t_text - the text, fulltext index on this one
And then search this table with fulltext search, group on t_fk_pid, and SUM() the relevance score and order by that. Should I perhaps split it up so that texts shorter than 5 words go in a seperate column, and treat that differently?

Any advice is appreciated!

Full Text Search Index
I have just set up a full text search index in order to use the MATCH... AGAINST search functionality.

Working fine, but I don't know how the index gets updated as new records are added to the indexed columns. Is this automatic or manual or cron job?

Unable To Create Full Text Search Index On A Column
I have installed MySQL 5.0 on my WinXP machine. I have created a test schema in which I have created a test table as follows:

Test_Table
{
Id => Integer and Primary Key
Name => Char(255)
Address => Char(255)
Tags => TEXT
}

Now I want to create a full text seach index on column Tags, but when ever I try to execute following SQL statement:

ALTER TABLE `TEST_TABLE` ADD FULLTEXT `Index_FTS`(`Tags`);

I get following error:

Error while executing query:
ALTER TABLE `TEST_TABLE` ADD FULLTEXT `Index_FTS`(`Tags`);
MySQL Error Number 1283
Column 'Tags' cannot be part of FULLTEXT index.

My table is MyISAM based table.

Does "load Index" Help For Fulltext Search?
(originally posted in database.myodbc by mistake)

I'm trying to speed up a fulltext search (MySQL 5.0.21, table has
670,000 records, about 900MBytes, machine has 1GB memory).

Fulltext search speed is quite variable -- sometimes really fast,
sometimes quite slow, so I'm trying to identify ways to make it more
consistent.

Most recently I tried "load index into cache testtable;". This
doesn't appear to have any significant impact on fulltext search speed
- some searches are a bit faster, some a bit slower. Should "load
index" improve mysql fulltext search performance?

"non-full-text" Search Index
I have a database table with a field that is indexed as a "full-text"
search, since I want the capabiity. However, I also want the ability to
quickly retrieve records from that table that are ins "sorted-order" by this
very same field.
Do I still need to create a "regular" non-full-text search index to have
this capability? Or will the "full-text" index be sufficient?

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

Full Text Index And Full Text Search On Several Columns And Indivdiual Columns
1. Say if I want to do the full text search on three columns, "title", "description", "keyword". The search could be done only on one column, or combine any two columns, or combine all three columns, do I need to create full text index for all these possiblilities, like full text index on each column, and then full text index on any two columns and then full text index on all three columns?

If I have to make so many different kind full text index, that will slow my database performance or not?

2. Will the relevance also be calculated based on which column, such as the "title" will have higher relevance than "keyword", the "keyword" will have higher relevance than "description"?

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?

Search: Remove Quotes From Database For Search
I am making a book site and currently in the database are book titles likes "John's Trip" and "Peter's new bike". I have a search function where the customer can type in "Peter's" and it will bring up everything with "Peter's" in it.

But i was wonderind is there any way i can search for "Peters" and "Johns".

I Have A Search Form To Search Through The DB.
I have a DB with 50,000 entries. I have a search form to search through the DB. If the recno=30123456 and you search the recno, you get that one file. what if i want a query that searches alll recno's that contain 30?? Can I do that using mysql or do I need to program that in my java app?

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.

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.