Problem with query slowness

[dreamwest]

I have over 2 million rows in a table, it only has 2 fields but the queries are taking 25-30 seconds to execute

 

Structure:

Field:  Type:            Colllation:                Extra:

id    int(11)                    auto_increment

title   varchar(200)  latin1_swedish_ci

 

Both fields have indexes on them, i have zero overhead on the table

 

Heres my row statistics too:

 

Row Statistics    Statements  Value

Format dynamic

Collation latin1_swedish_ci

Rows 2,335,312

Row length ø 130

Row size ø 261 B

Next Autoindex 2,336,209

Creation Nov 04, 2009 at 12:52 AM

Last update Nov 04, 2009 at 12:53 AM

Last check Nov 04, 2009 at 12:53 AM

 

 

When i search the database i use this:

 

SELECT  * FROM table WHERE title like '%".mysql_real_escape_string($search)."%' ORDER BY id DESC LIMIT 1, 15

[gizmola]

Yup!

 

You are table scanning every time, because mysql can not use an index when you issue a like query, and use a wildcard at the start of the query as you are doing.

 

LIKE 'Something%'  --- will use an index

LIKE '%Something%' -- no query can be used

 

 

This is not a mysql issue, it works this way with all relational databases.  An alternative is the full text index.

[dreamwest]

OK i made the title fulltext it brought the query down to 8 seconds. But the index only has 1 cardinality shouldnt it have over 2 million??

 

Indexes:

Keyname  Type            Cardinality  Field

PRIMARY  PRIMARY 2335548      id

title_2      FULLTEXT   1            title

[dreamwest]

I tries this

 

SELECT  * FROM table WHERE title like '".mysql_real_escape_string($search)."%' ORDER BY id DESC LIMIT 1, 15

 

It brought the query down to (0.002217 Seconds) which is great but i dont get many results, there has to be a way around this.....maybe split the search query up into separate words, then order by how many times each word appears...ill work on it

[dreamwest]

Actually that wont work either, the only way is to split the string:

 

table1:

lady walks her dog

 

to table2:

 

lady

walks

her

dog

 

Store the keywords in another table and assign an id to each keyword, then ppl would search the keyword table2 and associate the rows in table1 with the search.....the benefit - its supa fast.

 

This is how google must do it

[gizmola]

I tries this

 

SELECT  * FROM table WHERE title like '".mysql_real_escape_string($search)."%' ORDER BY id DESC LIMIT 1, 15

 

It brought the query down to (0.002217 Seconds) which is great but i dont get many results, there has to be a way around this.....maybe split the search query up into separate words, then order by how many times each word appears...ill work on it

 

So the fulltext index works very differently than a normal index because it's dealing with an internal score in regards to matching.  With that said it has the advantage of matching phrases.  I would fully explore it before you attempt to roll your own version of what it does, by breaking up everything into a giant keywords table, which inherently can't match phrases.

 

You do have to read a bit on how it works.  For example, you need to use the right query syntax --  in your case something like this I think:

 

"SELECT  * FROM table WHERE MATCH(title) AGAINST ('" . mysql_real_escape_string($search) . "') LIMIT 0, 15";

 

 

[fenway]

Careful... fulltext can be very evil.