I’ve created a simple mozilla extension search engine. Everytime I submitted a query, it would take forever for my suggestions to pop up. Frustrated at the latency, I looked at my log files to see this:
Processing SearchController#suggest_package (for 10.1.0.132 at 2007-06-27 18:52:32) [GET]
Session ID: 2836272b416b6f5e190f457b4c5644bb
Parameters: {“action”=>”suggest_package”, “package”=>”ray”, “controller”=>”search”}
Package Load (0.163366) SELECT * FROM packages WHERE (p_name LIKE ‘ray%’) GROUP BY p_name ORDER BY p_name LIMIT 25
Package Columns (0.000900) SHOW FIELDS FROM packages
Completed in 0.17745 (5 reqs/sec) | Rendering: 0.00013 (0%) | DB: 0.16427 (92%) | 200 OK [http://10.1.0.111/search/suggest_package?package=ray]
Notice anything interesting? How about the 92% DB load?
Disgusted by the results, I quickly realized that I didn’t index my queried field of 90k tuples so I created an index on that field by typing in the following into the mysql console:
create index name using btree on packages(p_name)
btw (btrees are the craziest things in the world, I got dominated in college trying to write one. I couldn’t get delete to save my life)
After creating the index, I submitted a second query and got:
Processing SearchController#suggest_package (for 10.1.0.132 at 2007-06-27 18:53:35) [GET]
Session ID: 2836272b416b6f5e190f457b4c5644bb
Parameters: {“action”=>”suggest_package”, “package”=>”ray”, “controller”=>”search”}
Package Load (0.002940) SELECT * FROM packages WHERE (p_name LIKE ‘ray%’) GROUP BY p_name ORDER BY p_name LIMIT 25
Package Columns (0.002396) SHOW FIELDS FROM packages
Completed in 0.01962 (50 reqs/sec) | Rendering: 0.00009 (0%) | DB: 0.00534 (27%) | 200 OK [http://10.1.0.111/search/suggest_package?package=ray]
27% ain’t that bad. I can live with 27% considering the fact that its running on a VM and im on a laptop.
Lesson to be learn. USE INDICIES!
Getting back into it 