speed up search on BIG table

mysql query is slow (3-6 seconds) can it be sped up

[hughie]

Hi All

I've a table with 24million rows. it has the following fields

ID - INT (primary key)
Side_A (varchar 4) indexed
Side_B (varchar 4) indexed
Date_Added (INT)

users will be doing a query on a few letters "AB" OR "BC" and at the moment i am doing

SELECT * FROM table WHERE Side_A LIKE '".$input."%' OR Side_B LIKE '".$input."%'

Is there any alternative, i would use FULLTEXT but that's configured for 4 letter words and I don't really want to change it.

Cheers,
Hughie

[Habtom]

I am sure you know this but...

SELECT * FROM table WHERE Side_A LIKE ...

May be mention field names if you are not using all

SELECT side_A, side_B FROM table . . .

[hughie]

Alas i use them all but there are only those 4 anyway so i doubt it's much of a hinderence in this instance. It's really the LIKE % search that's making the speed difference (oh for an monster server.)

[whoisgregg]

I'd add an "EXPLAIN" to the beginning and look at the output. Perhaps you could post back that output and we'll all take a look?

I don't know if you have two separate indexes or one for each field... I'm thinking a single index of both fields would be better. You might also need to add index hints so the engine knows which index to use.

[hughie]

It's actually running ok on my testing server but not great on my live (similar spec) so i'm just rebuilding the indexes.

I've a single index on each column.

will fetch that explain now.

[edited by: hughie at 2:12 pm (utc) on July 17, 2007]

[hughie]

The Query:
SELECT ID,Side_A, Side_B, Price FROM extended_SAVE WHERE Side_A = '".$value."' OR Side_B = '".$value."' LIMIT 1000 ");

The EXPLAIN

[0] => 1
[id] => 1
[1] => SIMPLE
[select_type] => SIMPLE
[2] => extended_SAVE
[table] => extended_SAVE
[3] => index_merge
[type] => index_merge
[4] => Side_A,Side_B
[possible_keys] => Side_A,Side_B
[5] => Side_A,Side_B
[key] => Side_A,Side_B
[6] => 5,5
[key_len] => 5,5
[7] =>
[ref] =>
[8] => 47
[rows] => 47
[9] => Using sort_union(Side_A,Side_B); Using where
[Extra] => Using sort_union(Side_A,Side_B); Using where

I've pulled this off a shorter version of table (100,000 records) for speed of testing.

[Lord Majestic]

LIKE is really the main issue here - typically it means table scan needs to be done, even though some databases (don't think MySQL is one of them) can optimise case where % sign is the last in the query. Using OR in queries also makes them slower, even though in case of table scan it does not really matter.

I notice that fields are just 4 chars, this makes the following approach very feasible: split each of the columns into 4: first will contain char 1 and that's it, second will contain chars 1,2 and that's it, third from 1-3 and final 4th will contain all chars.

This means that you can check length of input and run query on appropriate column using exact matching of = rather than LIKE.

This will add some programming overheads, but your matching should be fast so long as you index those columns to avoid table scan.

[hughie]

Hi Lord

Thanks for the pointers, however i really don't want to do any more table manipulation as it takes hours to make any adjustments to the data and I don;t want to increase the file size of the table any more than i need to.

I do have the query running very nicely on my local machine now, just going to try on my live server. I've added an individual index to Reg_A and Reg_B then reduced the length down to 3. This seems to have given a nice boost in speed.

However my live server is running mysql 4.x and local one is running 5. Hopefully that won't make too much of a difference....

Cheers,
hughie

[hughie]

nope, no good, results returned using the exact same db & index setup

Test server (2ghz single core 1.5gb ram mysql 5x) search time 3 seconds

Live server (2ghz single core 1gb ram mysql 4x) search time: 20 seconds

that's both with fresh queries (i.e. not cached)

cursed thing. Is it a RAM issue or possibly a mysql 4 vs 5 issue?

[Lord Majestic]

Optimisations are a compromise - you can gain speed but typically at the expense of size. I think in your case you really need to get MySQL 5 since it contains specific optimisation I mentioned that allows it to narrow down range when wildcard in LIKE predicate is not at start of it - MySQL 4 must be scanning the whole table.

[hughie]

Thanks lord, looks like mysql 5 it is, might go the whole hogg and go php5 at the same time.

Cheers for the help

Hughie

[hughie]

mysql 5 to the rescue, search time of 1.5 seconds compared to over 20!

many thanks for everyones help.