Uid column in bio table should be indexed
jonskulski - March 10, 2009 - 18:40
| Project: | Bio |
| Version: | 5.x-1.2 |
| Component: | Code |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
Jump to:
Description
A very, VERY common operation with the bio module is
SELECT nid FROM {bio} WHERE uid = %di.e. Looking up a bio for a particular user.
We have a table of roughly 180,000 users (which isn't that many), and here are our stats:
Without an index (default)
mysql> SELECT * FROM bio WHERE uid = 296493;
+--------+--------+
| nid | uid |
+--------+--------+
| 190837 | 296493 |
+--------+--------+
1 row in set (0.10 sec)mysql> ALTER TABLE bio ADD INDEX (uid);Query OK, 187644 rows affected (0.85 sec)
Records: 187644 Duplicates: 0 Warnings: 0
With an index
mysql> SELECT * FROM bio WHERE uid = 296493;
+--------+--------+
| nid | uid |
+--------+--------+
| 190837 | 296493 |
+--------+--------+
1 row in set (0.00 sec)I also need to do queries that look for people without bios and that drops from 10second to .5second
I attached a patch for bio.install that adds an update to do this.
| Attachment | Size |
|---|---|
| bio.add-index-uid.patch | 852 bytes |
#1
I committed this and it should be available in the next release. Thanks a lot!
#2
Automatically closed -- issue fixed for 2 weeks with no activity.